Question

Design a Excel spreadsheet to calculate the class’s grades. Consider the following requirements:

1. Each project will be graded from 0 to 10.
2. the contribution of each grade to the final grade are ( 7 team work assignments 10%, Project 1,2,3,4= 20%, mid term 35%, final 35%.)
3. The final test will be graded from 0 to 100, but your solution should allow different grades (i.e 80 points, 90, or 95). This flexibility is very important.
4. Program your solution to set a numeric final grade.
5. Program your solution to translate the numeric grade to the letter grade using the corresponding table

PLEASE INCLUDE AT LEAST 5 STUDENTS.

PLEASE EXPLAIN STEP BY STEP with formulas.

Please show steps by steps and explain the calculations.

Grading Evaluation

Deliverable	Final grade contribution
7 Team homework assignments	10%
Projects (1,2,3,and 4)	20 %
Midterm	35 %
Final test	35 %

Grade	Description
100-93 (A)	Outstanding. The student displayed exceptional grasp of the material. The student showed intellectual insight and original thought.
90-92.9 (A-)	Excellent. The student demonstrated a thorough grasp of the material with occasional errors and omissions. Assignments were thoroughly and completely done, with careful attention to detail and clarity and with evidence of intellectual insight.
89.9-88(B+) 87.9-83(B) 82.9-80(B-)	Very good work. The student demonstrated a good understanding of the course materials. Some assignments were either not completed or not properly done. There are some problems in applying the course concepts.
79.9-78(C+) 77.9-73 (C) 72.9-70 (C-)	Acceptable. The quality of the work was not always satisfactory but overall was passing. Assigned work not was always done and, when done, was inadequate. Performance on examinations and other work was generally weak with regard to understanding of subject, proper formulation of ideas, and thoroughness.
69-66(D)	Poor. The quality of work was not always satisfactory but overall was passing. Assigned work was not always done and, when done, was inadequate. Performance on examinations and other work was generally weak with regard to understanding of subject, proper formulation of ideas, and thoroughness.
65- (F)	Unacceptable performance.

Answer 1

Answer: Hello! find your spreadsheet here. This has been prepared to calculate the final grade of students. If you have any doubt, feel free to ask me, I would be very happy. thanks.

Step-1- First enter the percentage of each project/assignment that students got.

Step-2- Then calculate the total percentage they got. Used formula Autosum() on Home Tab-click on this and select the range that wants to add up values. Look at this picture.

Step-3- Calculate Grade as you gave criteria- Here I used IF() formula with AND() to calculate Grade according to percentage. First- if percentage 93-100 then grade should be- "A" -Formula is- (if(AND(f2>=93,f2<100),"A") F2 has percentage values if f2 is greater or equal 92 and less than 100 then "A". And() is used here because we compare two values and they should be true. f2 should be greater than or equal to 93 and less than 100. We have 5 criteria in question so we used 5 if here to check 5 criteria and display Grade if anyone true.

Step-4- Display Description according to grade. There are 6 criteria to check, if percentage 93 to 100 then "Outstanding" ,if 90 to 92.9 then "Excellent" ,if percentage 80 to 89.9 "very good", if percentage 70 to 79.9 "Acceptable", if percentage 66 to 69 "Poor" else "Non-Acceptable", these conditions checked with if() function you can see in image and displayed description.