Lab work due in 5-6 hours

CMPTR2 excel ProJECT

Advanced Calculus

Skills

Save a workbook with a new name

Enter formulas

Use absolute references

Fill adjacent cells with formulas

Create formulas using the COUNT function

Create formulas using the MEDIAN function

Create formulas using the MAX function

Create formulas using the MIN function

Apply conditional formatting to a range of cells

Control page breaks

Add print titles

Modify the worksheet orientation

Hints:_______________________________________________________________________

· Review the functions above, such as COUNT, MIN, etc.

· Review Absolute and Relative cell references

· Review entering formulas and functions.

· Review all of the above skills.

Project overview

Silvio D’Alessandro is a high school mathematics teacher in Bergen Regional High School in Columbus, Ohio. He has started using Excel to calculate the final grade for students in his Advanced Calculus class. The final grade is a weighted average of the grades received for the first three marking periods and the final exam. One way to calculate a weighted average is by multiplying each student’s marking period grade by the weight given to the marking period and then final exam, and then totaling the results. For example, consider the following marking period grades and final exam grade:

· Marking Period 1 = 84

· Marking Period 2 = 80

· Marking Period 3 = 83

· Final Exam = 72

If the marking periods exams are each given a weight of 15, 20 and 25 percent, in that order, and the final exam is given a weight of 40 percent, the weighted average of the four scores is:

84*0.15 + 80*0.2 + 83*0.25 + 72*0.4 = 81.3

Mr. D’Alessandro already entered the marking period grades for his students and formatted much of the workbook. He wants you to enter the formulas and highlight the top 15 overall grades in the class. 4LTR_Excel_Xtra1PJT_ FirstLastName_1.xlsx

STUDENT start FILE

4LTR_Excel_Xtra1PJT_ FirstLastName_1.xlsx (Note: Download your start file from ecampus.dcccd.edu)

Instructions

Open the file 4LTR_Excel_Xtra1PJT_FirstLastName_1.xlsx and save the file as 4LTR_Excel_Xtra1PJT_ FirstLastName_2.xlsx before you move to the next step (Note: you will substitute FirstLastName with your first and last names – ex. Jane Doughy will be 4LTR_Excel_Xtra1PJT_ JaneDoughy_2.xlsx )

· This document contains 2 worksheets – Documentation and Final Grades. In cell B4 of the Documentation sheet replace the FirstName LastName with your first and last names (say Jane Doughy).

In the Final Grades worksheet, in cell F16, enter a formula to calculate the weighted average of the first student’s three marking period grades and the final exam. The formula in cell F16 should use absolute references to the weights found in the range B6:B9, matching each weight with the corresponding grade. Use Auto Fill to copy the formula in cell F16 into the range F17:F45.

In cell F12, use the COUNT function to calculate the total number of final grades in the range F16:F45.

In cell C6, use the MEDIAN function to calculate the median grade for the first marking period.

In cell D6, use the MAX function to calculate the maximum grade for the first marking period.

In cell E6, use the MIN function to calculate the minimum grade for the first marking period.

In cell F6, calculate the range of scores for the first marking period, which is equal to the difference between the maximum and minimum score.

Calculate the median, maximum, minimum, and range scores for MP 2, MP 3, the Final Exam, and the Final Grades. (Hint: Repeat steps 4 through 7 for each of rows 7, 8, 9, and 10.)

Use conditional formatting to highlight the top 15 scores in the range F16:F45 with Light Red Fill with Dark Red Text.

Perform the following:

a. Insert a page break at cell A13,

1. repeat the first three rows of the worksheet in any printout,

1. print gridlines, and

1. set the worksheet to Landscape orientation.

Your completed worksheet should look like the Final Figure below. Save your changes, close the workbook and exit Excel. Follow the directions on the eCampus (or Blackboard) Web site to submit your completed project.

FINAL FIGURE

xCr PROJECT – Professor Egedigwe 3