Need help dialing in a grading formula [percentage to letter (100 to A+, 80 to B-, etc.)]

Album

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", "")))))))))))))

brianschmidt

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!:)

brianschmidt

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!:)

Album

Thank you so much! That works perfectly!