Can I create a formula with more than 8 IF statements?

P

Paul P

I have created the following formula:

=IF(Q12>=94,"5.7",IF(Q12>=86,"5.3",IF(Q12>=78,"5.0",IF(Q12>=69,"4.7",IF(Q12>=59,"4.3",IF(Q12>=49,"4.0",IF(Q12>=41,"3.7",IF(Q12>=21,"3.0",2))))))))

but I really want to add one or two more IF statements. It does not seem to
be possible.

Any suggestions
 
T

tony h

if it doesn't let you then there are three choices.
1. split the formula into two
2. set the break points and values in a table and do a vlookup
3 code the statement in vba as a formula and reference that

any preferences
 
B

Bob Phillips

7 nested statements is your limit. Try this

=VLOOKUP(Q12,{0,2;22,3;42,3.7;50,4;60,4.3;70,4.7;79,5;87,5.3;95,5.7},2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top