software

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},{quot; Equot;,quot;D-quot;,quot;Dquot;,quot;D quot;,quot;C-quot;,quot;Cquot;,quot;C quot;,quot;B-quot;,quot;Bquot;,quot;B quot;,quot;A-quot;,quot;Aquot;})

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?

You don't have an A I assume you mean A amp; A-

Try:

=LOOKUP(ROUND(C5,0),{0,60,63,67,70,73,77,80,83,87, 90,93},{quot;Equot;,quot;D-quot;,quot;Dquot;,quot;D quot;,quot;C-quot;,quot;Cquot;,quot;C quot;,quot;B-quot;,quot;Bquot;,quot;B quot;,quot;A-quot;,quot;Aquot;})

--
HTH

Sandy
with @tiscali.co.ukquot;Hubiequot; gt; wrote in message
...
gt;I have a grade spreadsheet for my teacher wife. Total points available,
gt; total points achieved, percent (grade). I'm then using a LOOKUP to
gt; populate
gt; a letter grade column.
gt;
gt; =LOOKUP(C5,{0,60,63,67,70,73,77,80,83,87,90,93},{quot; Equot;,quot;D-quot;,quot;Dquot;,quot;D quot;,quot;C-quot;,quot;Cquot;,quot;C quot;,quot;B-quot;,quot;Bquot;,quot;B quot;,quot;A-quot;,quot;Aquot;})
gt;
gt; The problem is rounding. The calculated percentage achieved...is a cell
gt; with a formula that displays as a whole number. The actual calculated
gt; value
gt; is NOT a whole number. So, the LOOKUP will display a 92.78 as an A, and a
gt; 93.01 as an A . Both actually round to a 93/A .
gt;
gt; Is there any way to avoid this annoyance?

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},{quot;
Equot;,quot;D-quot;,quot;Dquot;,quot;D quot;,quot;C-quot;,quot;Cquot;,quot;C quot;,quot;B-quot;,quot;Bquot;,quot;B quot;,quot;A-quot;,quot;Aquot;})--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=529029Either 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},{quot;Equot;,quot;D-quot;,quot;Dquot;,quot;D quot;,quot;C-quot;,quot;Cquot;,quot;C quot;,quot;B-quot;,quot;Bquot;,quot;B quot;,quot;A-quot;,quot;Aquot;})quot;Hubiequot; wrote:

gt; I have a grade spreadsheet for my teacher wife. Total points available,
gt; total points achieved, percent (grade). I'm then using a LOOKUP to populate
gt; a letter grade column.
gt;
gt; =LOOKUP(C5,{0,60,63,67,70,73,77,80,83,87,90,93},{quot; Equot;,quot;D-quot;,quot;Dquot;,quot;D quot;,quot;C-quot;,quot;Cquot;,quot;C quot;,quot;B-quot;,quot;Bquot;,quot;B quot;,quot;A-quot;,quot;Aquot;})
gt;
gt; The problem is rounding. The calculated percentage achieved...is a cell
gt; with a formula that displays as a whole number. The actual calculated value
gt; is NOT a whole number. So, the LOOKUP will display a 92.78 as an A, and a
gt; 93.01 as an A . Both actually round to a 93/A .
gt;
gt; Is there any way to avoid this annoyance?

Very nice...thanks everyone.

H

quot;daddylonglegsquot; wrote:

gt;
gt; I'd change the formula in C5 to round to the nearest integer, i.e.
gt;
gt; =ROUND(formula,0)
gt;
gt; where formula is your existing formula in C5, or alternatively round
gt; the lookup value in your LOOKUP formula, i.e.
gt;
gt; =LOOKUP(ROUND(C5,0),{0,60,63,67,70,73,77,80,83,87, 90,93},{quot;
gt; Equot;,quot;D-quot;,quot;Dquot;,quot;D quot;,quot;C-quot;,quot;Cquot;,quot;C quot;,quot;B-quot;,quot;Bquot;,quot;B quot;,quot;A-quot;,quot;Aquot;})
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=529029
gt;
gt;