Trying to reduce # of logical tests in "if" statement

C

christopherp

HI There,

I am trying to setup an if statement that only returns a result afte
all marks for the semester have been entered but I now have too man
tests in the formula.

I have tried to use an AND statement at the beginning of the formul
but I have had no luck so far.

Any help would be appreciated.

See below

=IF(E21="","incomplete",IF(E24="","incomplete",IF(E25="","incomplete",IF(E26="","incomplete",IF(E27="","incomplete",IF(E20>=80%,"Hig
Distinction",IF(E20>=70%,"Distinction",IF(E20>=60%,"Credit",if(e20=<50%,"fail"))))))))


:confused
 
J

JulieD

Hi

try
=IF(OR(E21="",E24="",E25="",E26="",E27=""),"incomplete",IF(E20>=80%,"High
Distinction",IF(E20>=70%,"Distinction",IF(E20>=60%,"Credit","fail"))))

Regards
JulieD
 
J

Jason Morin

Replace your first 5 IF statements with:

=IF(COUNTA(E21,E24:E27)=0,"incomplete",IF(...

HTH
Jason
Atlanta, GA
 
J

JE McGimpsey

one way:

=IF(COUNT(E21:E30)<10, "incomplete", LOOKUP(E20,
{0,"fail";0.5,"";0.6,"credit";0.7,"distinction"}))


Note that your criteria don't say what happens with values between 50%
and 60%.
 
A

Aladin Akyurek

Taking up JulieD's interpretation...

=IF(COUNTBLANK(E21:E27),"Incomplete",LOOKUP(E20+0.01*(LOOKUP(E20,C1:C5)=E20)
,{0,"Fail";0.6,"Credit";0.7,"Distinction";0.8,"High Distinction";1,"High
Distinction"}))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top