MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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