Operating on Non Calculated Values

[Guest]

I have a grade spreadsheet for my teacher wife. Total points available,
total points achieved, percent (grade). I'm then using a LOOKUP to populate
a letter grade column.

=LOOKUP(C5,{0,60,63,67,70,73,77,80,83,87,90,93},{"E","D-","D","D+","C-","C","C+","B-","B","B+","A-","A"})

The problem is rounding. The calculated percentage achieved...is a cell
with a formula that displays as a whole number. The actual calculated value
is NOT a whole number. So, the LOOKUP will display a 92.78 as an A, and a
93.01 as an A+. Both actually round to a 93/A+.

Is there any way to avoid this annoyance?

 

[Sandy Mann]

You don't have an A+ I assume you mean A & A-

Try:

=LOOKUP(ROUND(C5,0),{0,60,63,67,70,73,77,80,83,87,90,93},{"E","D-","D","D+","C-","C","C+","B-","B","B+","A-","A"})

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[daddylonglegs]

I'd change the formula in C5 to round to the nearest integer, i.e.

=ROUND(formula,0)

where formula is your existing formula in C5, or alternatively roun
the lookup value in your LOOKUP formula, i.e.

=LOOKUP(ROUND(C5,0),{0,60,63,67,70,73,77,80,83,87,90,93},{
E","D-","D","D+","C-","C","C+","B-","B","B+","A-","A"}

 

[Guest]

Either change the formula in C5 to include the round function ex:
=your_function would become =round(your_function,0)
Or include the round in the lookup:
=LOOKUP(round(C5,0),{0,60,63,67,70,73,77,80,83,87,90,93},{"E","D-","D","D+","C-","C","C+","B-","B","B+","A-","A"})

 

[Guest]

Very nice...thanks everyone.

H