SOLVED

# #DIV/0! Error

Copper Contributor

# #DIV/0! Error

I have a spreadsheet of grades that require a scaled score. The original sheet is downloaded from our textbook computer program. I have added a line under the raw score to create a row of scaled scores and created a formula to correctly scale the scores. Now I need to average the scaled scores. Even when all the grades are complete, I'm still getting the #DIV/0! error message.

If it matters, the formula used on the Scaled Scores row is:

=IF(C8="Not Started", " ", IF(C8="In Progress", " ", IF(C8>81, "100", IF(C8>=61, "93", IF(C8>=41, "85", IF(C8>=21, "75", IF(C8>=1, "60", IF(C8=0, "50"))))))))

 Realize Gradebook Export All scores shown as percentages Date Range: August 7, 2023 - September 6, 2023 Program Selected: All Programs Student Name Average Score 8/23/2023 - 1-1: Quick Check 8/24/2023 - 1-2: Quick Check 8/25/2023 - 1-3: Quick Check 8/26/2023 - 1-4: Quick Check 8/29/2023 - 1-5: Quick Check 8/30/2023 - 1-6: Quick Check 8/31/2023 - 1-7: Quick Check 9/1/2023 - 1-8: Quick Check 9/2/2023 - 1-9: Quick Check 9/7/2023 - Topic 1 Online Assessment S1 Raw 85 80 Not Started Not Started Not Started 83 100 80 80 60 73 S1 Scaled 93 100 100 93 93 85 93 #DIV/0! S2 Raw 28 40 20 0 0 33 33 20 20 Not Started Not Started S2 Scaled 75 60 50 50 75 75 60 60 S3 Raw 33 60 60 0 Not Started Not Started Not Started 0 20 20 33 S3 Scaled 85 85 50 50 60 60 75 S4 Raw 36 40 40 40 40 17 17 20 20 0 40 S4 Scaled 75 75 75 75 60 60 60 60 50 75 #DIV/0! S5 Raw 63 80 In Progress In Progress 40 83 83 40 40 20 80 S5 Scaled 93 75 100 100 75 75 60 93 S6 Raw 68 100 80 60 40 83 50 In Progress 60 20 40 S6 Scaled 100 93 85 75 100 85 85 60 75
2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: #DIV/0! Error

=IF(C8="Not Started", " ", IF(C8="In Progress", " ", IF(C8>81, 100, IF(C8>=61, 93, IF(C8>=41, 85, IF(C8>=21, 75, IF(C8>=1, 60, IF(C8=0, 50))))))))

In the attached example i've entered this formula in C9 and filled across C9:L9. The average formula in cell M9 returns the intended result. I've changed e.g.   "85" to 85 because  "85" is a text value and 85 is a number value which can be used for calculation.

=AVERAGE(C9:L9)

# Re: #DIV/0! Error

Worked perfectly. Thank you!