Need help dialing in a grading formula [percentage to letter (100 to A+, 80 to B-, etc.)]
Hey all,
This formula works most of the time, but it won't show the letter grade all the time, I think when its close to an in-between number. Any help making it function 100% of the time and not show an empty cell would be appreciated
=IF([Week 04/01 Results]5 > 0.96, "A+", IF(AND([Week 04/01 Results]5 >= 0.93, [Week 04/01 Results]5 <= 0.96), "A", IF(AND([Week 04/01 Results]5 >= 0.9, [Week 04/01 Results]5 <= 0.92), "A-", IF(AND([Week 04/01 Results]5 >= 0.87, [Week 04/01 Results]5 <= 0.89), "B+", IF(AND([Week 04/01 Results]5 >= 0.83, [Week 04/01 Results]5 <= 0.86), "B", IF(AND([Week 04/01 Results]5 >= 0.8, [Week 04/01 Results]5 <= 0.82), "B-", IF(AND([Week 04/01 Results]5 >= 0.77, [Week 04/01 Results]5 <= 0.79), "C+", IF(AND([Week 04/01 Results]5 >= 0.73, [Week 04/01 Results]5 <= 0.76), "C", IF(AND([Week 04/01 Results]5 >= 0.7, [Week 04/01 Results]5 <= 0.72), "C-", IF(AND([Week 04/01 Results]5 >= 0.67, [Week 04/01 Results]5 <= 0.69), "D+", IF(AND([Week 04/01 Results]5 >= 0.63, [Week 04/01 Results]5 <= 0.66), "D", IF(AND([Week 04/01 Results]5 >= 0.6, [Week 04/01 Results]5 <= 0.62), "D-", IF(AND([Week 04/01 Results]5 >= 0.01, [Week 04/01 Results]5 <= 0.59), "F", "")))))))))))))
Best Answer
-
Exactly! If any of the results land between grades in decimal places not represented here, the right letter grade may not show. Here's what I would input instead.
=IF([Week 04/01 Results]5 > 0.96, "A+", IF(AND([Week 04/01 Results]5 >= 0.93, [Week 04/01 Results]5 <= 0.96), "A", IF(AND([Week 04/01 Results]5 >= 0.9, [Week 04/01 Results]5 < 0.93), "A-", IF(AND([Week 04/01 Results]5 >= 0.87, [Week 04/01 Results]5 < 0.9), "B+", IF(AND([Week 04/01 Results]5 >= 0.83, [Week 04/01 Results]5 < 0.87), "B", IF(AND([Week 04/01 Results]5 >= 0.8, [Week 04/01 Results]5 < 0.83), "B-", IF(AND([Week 04/01 Results]5 >= 0.77, [Week 04/01 Results]5 < 0.8), "C+", IF(AND([Week 04/01 Results]5 >= 0.73, [Week 04/01 Results]5 < 0.77), "C", IF(AND([Week 04/01 Results]5 >= 0.7, [Week 04/01 Results]5 < 0.73), "C-", IF(AND([Week 04/01 Results]5 >= 0.67, [Week 04/01 Results]5 < 0.7), "D+", IF(AND([Week 04/01 Results]5 >= 0.63, [Week 04/01 Results]5 < 0.67), "D", IF(AND([Week 04/01 Results]5 >= 0.6, [Week 04/01 Results]5 < 0.63), "D-", IF(AND([Week 04/01 Results]5 >= 0.01, [Week 04/01 Results]5 < 0.6), "F", "")))))))))))))
Hope this helps!:)
Answers
-
Exactly! If any of the results land between grades in decimal places not represented here, the right letter grade may not show. Here's what I would input instead.
=IF([Week 04/01 Results]5 > 0.96, "A+", IF(AND([Week 04/01 Results]5 >= 0.93, [Week 04/01 Results]5 <= 0.96), "A", IF(AND([Week 04/01 Results]5 >= 0.9, [Week 04/01 Results]5 < 0.93), "A-", IF(AND([Week 04/01 Results]5 >= 0.87, [Week 04/01 Results]5 < 0.9), "B+", IF(AND([Week 04/01 Results]5 >= 0.83, [Week 04/01 Results]5 < 0.87), "B", IF(AND([Week 04/01 Results]5 >= 0.8, [Week 04/01 Results]5 < 0.83), "B-", IF(AND([Week 04/01 Results]5 >= 0.77, [Week 04/01 Results]5 < 0.8), "C+", IF(AND([Week 04/01 Results]5 >= 0.73, [Week 04/01 Results]5 < 0.77), "C", IF(AND([Week 04/01 Results]5 >= 0.7, [Week 04/01 Results]5 < 0.73), "C-", IF(AND([Week 04/01 Results]5 >= 0.67, [Week 04/01 Results]5 < 0.7), "D+", IF(AND([Week 04/01 Results]5 >= 0.63, [Week 04/01 Results]5 < 0.67), "D", IF(AND([Week 04/01 Results]5 >= 0.6, [Week 04/01 Results]5 < 0.63), "D-", IF(AND([Week 04/01 Results]5 >= 0.01, [Week 04/01 Results]5 < 0.6), "F", "")))))))))))))
Hope this helps!:)
-
Thank you so much! That works perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!