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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!