Operating on Non Calculated Values
 

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?

Operating on Non Calculated Values
 

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

with @tiscali.co.uk


"Hubie" wrote in message
...
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?

Operating on Non Calculated Values
 

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 round
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"})


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=529029

Operating on Non Calculated Values
 

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


"Hubie" wrote:

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?

Operating on Non Calculated Values
 

Very nice...thanks everyone.

H

"daddylonglegs" wrote:


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 round
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"})


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=529029