Tuesday, 24 March 2015

Ms Excel Assignments



Excel Assignments #10: Course Grades
  1. Prepare the following spreadsheet, being sure to use appropriate formulas and formats.
Course: ICT
Lecturer: Mr Atisa Paul
Jan - May
Name
Student No.
KEYBOAD
WORD
EXCEL
ACCESS
Total
Percentage
Total for Subject
30
35
25
30

%
Dave K.
1203
25
31
19
21
 ?
 ?
Samuel
2219
10
15
10
14
 ? ?
Victoria
 1721
 22
 30
 20
 22
 ? ?
 Juliana
1604
24
29
20
20
? ?
Joel
1798
26
29
22
28
? ?
Mary
1115
25
30
20
25
? ?
Henry
1214
29
32
23
27
? ?
Sarah
2021
19
21
18
20
? ?
John
2015
22
21
19
18
? ?
Suzanne
1320
12
12
14
9
? ?







Average:
 ?
 ?
 ?
 ?



  1. To calculate the Percentage, divide the Student’s Total Mark by the Total Possible Mark. The Total Possible Mark requires an absolute reference since you must refer to this same cell for each calculation (the darker cell - you might refer to it with $G$5).
  2. Format the Percentage column to Percent with no decimal places.
  3. Format the Average row to 2 decimal places.
  4. Sort the students by Percent (highest to lowest), and then Student Number (lowest to highest).
  5. Add a column titled "Pass/Fail".  Using the IF command display the word 'Pass' if the percent is greater than or equal to 50, display the word 'Fail' if the percent is less than 50.
  6. Format the table with a border, title shading, appropriate graphic, motto of St. David "Working Together to be the Best we can be" using word art, centered horizontally and vertically.
  7. Add your name using a header and the date and time as footer to the spreadsheet, save and print preview.
Assignment #11: Assignment Grades
Create the following spreadsheet:
Student
Assignment1
Assignment1 %
Assignment2
Assignment2 %
Average %

35

55


Atisa
34

45


Sure
31

49


Patrick
28

51


Samuel
22

36


Mike
18

38


Jane
7

31


Maria
29

36


Lewis
26

29








Average





Maximum





Minimum






  1. The numbers in B2 and D2 are the grade the assignments were marked out of. Use a formula with an absolute cell reference to calculate the assign1 % and assign2 %. These numbers are the mark the student received on the assignment divided by the mark the assignment was out of. Display the answer as a percentage with 1 decimal place
  2. Use functions to display the average, maximum and minimum of the assign1, assign1%, assign2 and assign2 % columns.  Format the average, maximum and minimum to all have 1 decimal place.
  3. Use a formula to calculate the numbers in the average% column, this is the assign1% added to the assign2% divided by 2 – beware of BODMAS!
  4. Format the average5 column to display 1 decimal place.
  5. Align titles and numbers to make your spreadsheet look professional.
  6. Add your name using a header and the date and time as a footer to the spreadsheet, save and print preview. 
 
Ms Excel Assignments Continued...
Question 3
Create the following data in a new and then rename it as Q3

Paulatech Car Sales for the Year 2015

Car Make                     Western            Eastern                        Nairobi                        Coast
Toyota Hilux                  400                   250                   600                   450
Nissan Turbo                 312                   315                   700                   345
Nissan Sunny                 300                   200                   350                   254
Toyota Starlet                 275                   156                   465                   350
Peogeout 406                 250                   340                   550                   156
Ford Sierra                    150                   100                   150                   85
Opel Astra                     50                     15                     200                   153
           
·         Calculate the total car sales and average for each region and car make
·         Insert a column between Eastern and Nairobi for Nyanza whose sales are 12% more than the Eastern region
·         Sort the data so that the car make with the highest sales comes first
·         Draw a colum chart to show Paulatech car sales for the year 2015
·         Delete the row for Ford sierra
·         Put borders to your data

Question 4
Create the following data in a new and then rename it as Q4

Retirement scheme

Employee Id                Salary               % Contribution             Contribution Amount
A345                            32000               3%
M322                            45200               5%
A543                            25000               2%
A587                            28500               3%
M545                            38600               5%
A233                            25300               2%

·         Calculate the contribution amount for each employee
·         Put borders to your data

Question 5
Create the following data in a new and then rename it as Q5

Description                  Quantity           Cost per item    Amount           
Note Pad                       202                   2.85     
Highlighter Pen               280                   2.95     
Ball Point Pen Blue         520                   2.50     
Ball Point Pen Red          340                   2.50     
Ball Point Pen Green       250                   2.50     
Exercise Book 1B           35                     4.95     
Cello-tape                      75                     2.95     
Manila Folders                100                   2.95     
A4 Refill Pad                 90                     5.95

·         Calculate the amount to be paid for each item
·         Calculate the total cost of the order
·         Put borders to your data

Arrange the Item alphabetically (A-Z)
Question 6
Create the following data in a new and then rename it as Q6

Onchwari, Simba, Pamela and Nyabeto had Tea, Sausages and Bananas for breakfast. They took one sausage, two sausages and one sausage respectively.

In addition they each took a cup of tea and two bananas. Tea, Sausage and Bananas cost kshs. 10, 15, and 5 respectively.
By naming columns A, B, C, D……………………….and rows 1, 2, 3, 4……

(a)     Construct a worksheet showing the above information
(b)     Calculate the total expenditure by Pamela
(c)     Calculate the total number of sausages taken
(d)     The cost of the cheapest item

 
Propellerads