ARRAY OR IF STATEMENT
Posted by MARTHA on September 26, 2001 11:41 AM
Remember I asked about giving a letter grade to a value. I found a formula. Here it is to those of you who can pass it on:
=IF(AND(D3>=90,D3<=100),"A",IF(AND(D3>=80,D3<=89),"B",IF(AND(D3>=70,D3<=79),"C",IF(AND(D3>=60,D3<=69),"D",IF(AND(D3>=1,D3<=59),"F","")))))
Thanx guys for your help,
Martha
Posted by IML on September 26, 2001 11:57 AM
The other option...
with your data would be
=HLOOKUP(D3,{0,60,70,80,90;"F","D","C","B","A"},2,1)
this would be better if you had a grade like 69.5 that isn't covered in your if statement.
Posted by T on September 26, 2001 11:57 AM
FYI-
This can be simplified to
=IF(D3>=90,"A",IF(D3>=80,"B",IF(D3>=70,"C",IF(D3>=60,"D","F"))))
Posted by Aladin Akyurek on September 26, 2001 12:04 PM
Why not VLOOKUP?
Martha --
I'd rather propose to use:
=VLOOKUP(D3,{1,"F";60,"D";70,"C";80,"B";90,"A"},2)
Aladin
Posted by martha on September 26, 2001 1:43 PM
Re: The other option...
Both of you are right about using a lookup rather than the ifstatement. I guess I was a little ignorant to how far a lookup can go. Thanx again.
Martha
