Forum Discussion
Solved
#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 |
=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)
=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)
- Worked perfectly. Thank you!
