Excel Logical String Problem

  • Thread starter Thread starter pcmason
  • Start date Start date
P

pcmason

I'm trying to create a column in Excel 2000 that converts numerica
grade ranges to the corresponding letter grades. I can't get the strin
written correctly.

For example I want to convert 1 through 0.98 to A+, 0.97 through 0.9
to A, 0.94 through 0.92 to A-, and so forth. I think in total ther
will be 13 nested arguments to cover the entire scoring range. Th
example below and variations of it have not been working (P5 is th
cell I want to convert):

=IF((P5=1-0.98,"A+"),IF(P5=0.97-0.95,"A"),IF(P5=0.94-0.92,"A-"))

Any advice would be much appreciated. Thank you.
Phi
 
Phil,

Create a table in say T1:U6 (as an example)

T1: 0 U1: F (or the last value)
T2: .8 U2: C
T3: .9 U3 B
T4: .95 U4: A-
T5: .97 U5: A
T6: .98 U6: A+

Then get the grade with

=VLOOKUP(K2,$T$1:$U$6,2)

where K2 holds the student score. Just amend and extend the table to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob gave you an answer, but I thought I would let you know what the problem
was with your approach for future reference. When you use something like
P5=1-0.98 for the logical test in the if statement it does not return true
for values of P5 between 1 and 0.98, it actually does subtraction and
compares what's in P5 to 0.02 so if the grade was 0.02 the letter grade
would end up being A+. What you need is something like And(P5<=1,P5>=0.98).
Also you shouldn't have the )'s after the true value

Sue
 
Back
Top