Kuwait University

College of Life Sciences

Information Science Department

ISC 100 - Fundamentals of Personal Computers

Assignment 5

Due Date: Tuesday 7 December, 2014

Answer the following questions:

  1. [15 marks] Given the following formula written in cell F2: =B1+$D4+C$5+$E$6 of the workbook below, write the resulting formula and result value when F2 is copied to F4, to G4, and to E1. Also write down the result for the other formulas. Use the figure below to find the Result of each formula.

Formula / Result
F2 / =B2+$D4+C$5+$E$6
F4
G4
E1
L1 / =If(B2 = (E5+C3), B2 * E6 * E3, Min(B2,E6))
B7 / =Average(B1:C3)

  1. [10 marks] Answer the following questions:
  2. Write an excel formula to compute the average of the values in cells from DD22 to DD100 and from cells CC1 to CC100.
  1. What is the value for the formula = F6 – D6/10 + E6/2 if cells D6, E6 and F6 contain the numbers 20,10 and 60 respectively?
  1. [35 marks] Use MS-Excel to calculate your payment for one week work. Use the given data below and do the following:
  2. Insert a row and enter your name where indicated.
  3. Enter the column and row labels as shown below
  4. Add the following formulas
    Total Hours = Regular Hours + Overtime Hours
    Pay = (Regular Hours * Hourly Wage) + (Overtime Hours * 1.5 * Hourly Wage)
    Totals for Regular Hours, Overtime Hours, Total Hours & Pay
  5. Change the worksheet name to Week 1

Your Name Here
(centered across columns)
Week 1 Time Sheet
Hourly Wage / 9.50
Regular
Hours / Overtime
Hours / Total
Hours / Pay
Sunday / 8 / 2
Monday / 7 / 0
Tuesday / 7 / 0
Wednesday / 8 / 1
Thursday / 8 / 4
Totals
  1. Create a copy of the worksheet in the same workbook and name it Week 2
  2. Change the values as follows:

Week 2 Time Sheet
Hourly Wage / 10.25
Regular
Hours / Overtime
Hours / Total
Hours / Pay
Sunday / 8 / 0
Monday / 8 / 3
Tuesday / 7 / 0
Wednesday / 8 / 4
Thursday / 8 / 1
Totals
  1. Create a 3-D pie chart in a new sheet showing the payment for each weekday from Week 2.
  2. Remove the legend and add labels for each pie slice with leader lines
  3. Add a chart title "Weekday Payroll" and format it using at least a 22 point font size.
  4. Change the color of the labels and title
  5. Rename the chart worksheet to Pie Chart
  1. Create a Clustered bar chart in a new sheetcomparing the payment for each weekday from Week1 and Week 2.
  2. Adda legend showing the different weeks and make sure your horizontal axis showing the weekdays.
  3. Add a chart title "Weekly Payroll comparison" and format it using at least a 22 point font size.
  4. Change the color of the labels and title
  5. Rename the chart worksheet to Clustered Bar Chart

Note:You are responsible for all decisions regarding cell width, format, labels, and formulas.

What to hand in:submit a hardcopy of your solution that shows the results of Week 1, Week 2,the 3-D pie chart, and the clustered bar chart.