HI, I already finish the step on I,and you can start to do it from J.

and please follow the requirements carefully make sure you finish all the details.

Directions:

• Follow the instructions listed on the next pages.
• Enter your name on cell A1 of the worksheet.
• Submit your RecitationProject.xlsx file to Sakai using the Sakai->Test & Quizzes link by following these 3 steps after scrolling towards the bottom of the instructions page:
  • Browse (select your file)
  • Upload your file
  • Submit your file

Instructions:

Grades spreadsheet

1. Go to Sakai → CS170 Gradebook page and do the following:
   1. Select the Gradebook Items, Grades, Due Dates and Comments columns. Do not select the column headings, only the items and their information.
   2. Copy the selection by right-clicking on it and choosing the option Copy.
   3. Start Excel, create a Blank worksheet.
   4. Select the cell C3 on the worksheet and paste the information copied. Widen the C column width.
      • Rename the worksheet as Grades.
   5. Delete column E: click on the E letter that identifies that column and then right -click and select Delete (this is needed since that column came with Data format).
   6. Add the following entries to the list of activities on the C column (below the last activity listed): Final Exam Part 1, Final Exam Part 2and Final Exam Part 3.
   7. Starting on the cell E3, type the maximum number of points possible for each Gradebook Item:
      • 2 points for Assignment 1
      • 20 points for the other Assignments and the Recitation Project
      • 80 points each for Exams 1 and 2
      • 120 for the Final Exam (simply ignore it if this entry is not listed yet)
      • Note: If there is an item for which you do not have a grade yet, do not enter the maximum points for it.
   8. On row 28 do the following:
      • Enter the label “Totals” on C28
      • The total points obtained will be calculated on D28. Since there might be some blank cells, an appropriate function for this cell is SUMIF.
        • Arguments for SUMIF:
          • range: the list of scores on the D column (including blank cells for the activities with no scores yet)
          • criteria: greater than or equal to 0 (express this using Excel notation)
          • sum_range: the list of scores on the D column (including blank cells for the activities with no scores yet)
      • Copy the function from D28 to E28 using the fill handle.
   9. On row 29:
      • Enter the label “Performance” on C29.
      • On D29 enter a formula to calculate your Performance % by simply dividing your total points by the total maximum points (those numbers are on row 28).
      • Format your performance with Percentage style with 1 decimal.
   10. On G3, start a table of equivalences between % points (on the G column) and letter grades (on the H column). Use the following Grading table which is derived from the Grading section of the Syllabus (once completed the Grading table should start at G3 and end at H9.)

1. • Enter the label Letter Grade on C30.
   • On D30 insert the VLOOKUP function to calculate the current letter grade based on your Performance % and the Grading table created on G3:H9.
     • The arguments for the VLOOKUP function are:
       • lookup_value: your numeric performance % (from D29).
       • table_array: the table that converts percentages into letter grades which you created starting on G3.
       • col_index_num: 2 (since the second column contains the letter grades).
2. What-If Analysis:
   • Now that you have calculated a letter grade for your current scores, you will run some simulation Scenarios to evaluate the possible effect of the Final Exam.
   • The formula entered on cell D28 which currently contains SUMIF needs to be modified to process the hypothetical Final Exam scores for each of its parts.
   • To process the Second Chance option, the formula on D28 needs to be expanded. Instead of just =SUMIF(…) the format will be: =SUMIF(…) + IF(…) + IF(…)
   • • The first IF that needs to be added – use the Formula bar to add it – will have the following arguments (use Excel notation; value_if_false does not need an entry):
       • Logical_test: 2 x Score of Final Exam Part 1 > Score of Exam 1
       • Value_if_true: (2 x Score of Final Exam Part 1) – Score of Exam 1
       • Value_if false:

At this point D28 should have the following format:

=SUMIF(…) + IF(…)

• The second IF that needs to be added will have the following arguments (use Excel notation; value_if_false does not need an entry):
  • Logical_test: 2 x Score of Final Exam Part 2 > Score of Exam 2
  • Value_if_true: (2 x Score of Final Exam Part 2) – Score of Exam 2
  • Value_if false:

At this point D28 should have the following format:

=SUMIF(…) + IF(…) + IF(…)

• Some numbers have to be added to the rows where Final Exam Part 1, Final Exam Part 2 and Final Exam Part 3 are located.
  • On the D column of those rows place some hypothetical numbers (Example: 32 on each of those three cells).
  • The E column for those rows should have 40 on those cells since the maximum score for each part of the Final Exam is 40.
• At this point, the What-If Analysis may start. Click on D30, then on the Data tab → What-If Analysis → Scenario Manager.
  • Proceed to create at least three Scenarios with different data for the Changing Cells (the 3 cells on the D column with the hypothetical scores for the parts of the Final Exam).
  • The Result Cell when closing the Scenario Manager – by clicking the Summary button – is D30.
• After generating the Scenario Summary, save your work and submit it to Sakai → Test and Quizzes section.
  • Follow these 3 steps after scrolling towards the bottom of the instructions page:
    • Browse (select your file)
    • Upload your file
    • Submit your file