IF conditions

[Guest]

Is there a way of getting the FUNCTION wizard to help you through all
available nested IFs? I can manage fine with the 3 fill-in boxes but get
confused and have to spend ages perfecting multiple conditions like these

=IF(U5>=80%,"GRADE A",IF(U5>=70%,"GRADE B",IF(U5>=60%,"GRADE
C",IF(U5>=50%,"GRADE D",IF(U5>=40%,"GRADE E",IF(U5<40%,"FAIL"))))))

 

[ed]

I gave up on the Function Wizzard. It's pretty easy to read your
formula. When they're more complex I put in the values, one a t a
time, and see what results I get.

ed

 

[Bob Phillips]

You should learn to use LOOKUP. Put the values in a table like so

0 FAIL
40% GRADE E
50% GRADE D
60% GRADE C
70% GRADE B
80% GRADE A


and us a formula like

=VLOOKUP(U5,$A$1:$B$10,2,TRUE)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thanks for the reply.

I can sort of manage lookups but not well. I need ranges rather than values
- anything below 40% is FAIL, not just 0; 40-49% is D, not just 40% and so on.

The reason I posted the question about the wizard is that I've had it
switch itself on for a condition after the 3rd but have no idea how this
happened and can't make it happen again. Inserting one condition at a time
was easy, and I wish I could do this for all those I need.

 

[Paul B]

Ken,

"I need ranges rather than values - anything below 40% is FAIL, not just 0;
40-49% is D, not just 40% and so on"

The Lookup formula that Bob gave you does that, will give you and E not D as
per your request, E????, did you try it?

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

No but I'm convinced! I will!
--
Ken Knight

Ken,

"I need ranges rather than values - anything below 40% is FAIL, not just 0;
40-49% is D, not just 40% and so on"

The Lookup formula that Bob gave you does that, will give you and E not D as
per your request, E????, did you try it?

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003