Absolute Address & What-If Analysis

Part One:  Enter Data and Format Cells as below:  (You can copy and paste it to EXCEL File)

Grade Book
(Enter time / date stamp here)
Weight 30% 20% 20% 30%  
NAME HW1 HW2 HW3 Average HW EX1 EX2 Final Exam Semester Score extra credit Final Score Grade
Bagman, A. 51 60 70   76 72 70        
Brown, R. 73 90 78   82 87 89        
Clemens, M. 82 87 70   78 89 85        
Daley, F. 66 70 67   65 67 82        
Hill, B. 35 56 77   56 58          
Incan, P. 37 70 80   72 90 80        
King, S. 95 89 89   90 100 100        
Lee, C. 62 54 83   75 74 76        
Miller, L. 93 99 80   97 93 92        
Nichols, S. 84 76 45   85 86 78        
Olive, R. 100 80 90   88 89          
Rice, G. 80 50 78   60 67 68        
Taylor, G. 88 76 70   50 60 70        
Ward, R. 54 77 67   70 70 67        
Average                      
Maximum                      
Minimum                      
cutting point for extra credit 90                    
% for extra credit 3%                    

Part Two:  Do Calculations.

  1. Enter time stamp (=now()) on the second row.

  2. Use AutoSum button (Auto Average) to calculate the column of "Average HW".

  3. Use multiplication and addition to calculate the column of "Semester Score".
    The formula is followed.
    Semester Score = weight of Average HW * score of Average HW + weight of EX1 * score of EX1 + weight of EX2  * score of EX2 + weight of Final Exam * score of Final Exam.
    Press "F4" key to fix the addresses of weights. (Absolute Address) For example,
    =$E$3*E5+$F$3*F5+$G$3*G5+$H$3*H5 for the cell of I5.

  4. If a student has average HW greater than 90, he/she will get 3% of his/her semester score as his/her extra credit.  Otherwise, no extra credit is applied.
    Note that we use the addresses for 90 and 3% since we can change the criteria for what-if analysis.

  5. The final score of semester is counted from extra credit and semester score.

  6. Use AutoSum button (Auto Average, Auto Max, Auto Min) to calculate Average, Maximum, and Minimum on the bottom three rows.
    Make sure that you have the right range for each function.

Part Three: Copy information from one sheet to another sheet.

  1. Click the spot left to A and above 1 to highlight enter sheet.
  2. Copy it.
  3. Go to sheet 2 and paste it.  At this point, you have two identical pages.
  4. Rename sheet 1 as "Calculations" and sheet 2 as "What-IF".

Part Four: What-If Analysis.

There are two students missing the final exam.  Both have excuses and plan to make-up the test.

  1. Student Olive is looking for a grade "A" which needs 90 or above for the final score.
    What score does she have to get in the final exam in order to get an "A"?
    Hint:  use Data / What-if Analysis / Goal Seek to find it.

  2. Can student Hill get a "C" which requires 70 or above for the final score?

  3. Can student Hill get a "D" which requires 60 or above for the final score?

  4. There is a typo on the HW1 of student Incan.  It should be 87, not 37.  Please correct it.
    After correct it, what is the final score of the student Incan?
    Hint:  The result of calculation will be changed by computers after the data has been changed.

  5. After taking the make-up final tests, student Olive scores 90 and student Hill scores 57.
    What will be each of their grades?

Part Five: Sort Data.

  1. Highlight the entire page of "What-IF" sheet and copy it.

  2. Paste it to sheet 3 and rename sheet 3 as "Sort by Final Score".

  3. Click the cell "Final Score", then hold the Shift key and click the cell "Ward, R."
    Identify the beginning cell and the ending cell for highlight a area.

  4. Click on the icon of Z to A 

  5. Assign grades manually based upon the following distribution: Since the final scores were sorted, it is easy to decide grades.

      Final score: 90 and above A
                        80 and above B
                        70 and above C
                        60 and above D
                        below 60 F

  6. Insert a new worksheet and name it "Sort by Name".

  7. Copy the information from previous sheet (Sort by Final Score) and sort the data by ascending order of student's names.

Part Six: Show Formula Page

  1. Copy the last page to a new sheet and name it as "Formula".
  2. Use Ctrl + ~ to show formula.

Part Seven: Pie Chart and Cylinder Charts

  1. Make another table to summarize the number of students in each grade as below.

    Grades

    Counts

    A

    3

    B

    3

    C

    3

    D

    4

    F

    1

  2. Make a pie- chart as below.

  3. Make the following cylinder charts: Can you understand how to compare them?

    Use Ctrl key to highlight the necessary information A4:D4 & F4:H4 & A19:D21 & F19:H21.
    Click Insert / Column / Cylinder.
    Under Chart Tools / Design / Data, click Switch Row/Column.