software

I have the folowing IF statment that works well.

in quot;P11quot;
=IF(M11=90,10,IF(M11=100,20,IF(M11=110,30,IF(M11=1 20,40,IF(M11=130,50,IF(M11=140,60,IF(M11=150,70,IF (M11gt;159,80,))))))))

The value in P11 can be from 10 to 80 based on that statment. I have tried
to add in the front of the IF statment quot;IF(M11=0,0,quot; making it like this
=IF(M11=0,0,IF(M11=90,10,IF(M11=100,20,IF(M11=110, 30,IF(M11=120,40,IF(M11=130,50,IF(M11=140,60,IF(M1 1=150,70,IF(M11gt;159,80,)))))))))
and it always fails with an error. What am I doing wrong?

Hi,

This might help:

www.cpearson.com/excel/nested.htm

Regards,
Bondi=IF(M11gt;159,80,IF(OR(M11lt;90,MOD(M11,10)lt;gt;0),0*(M11/10-8)*10))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;BThrasherquot; gt; wrote in message
news
gt; I have the folowing IF statment that works well.
gt;
gt; in quot;P11quot;
gt;
=IF(M11=90,10,IF(M11=100,20,IF(M11=110,30,IF(M11=1 20,40,IF(M11=130,50,IF(M11
=140,60,IF(M11=150,70,IF(M11gt;159,80,))))))))
gt;
gt; The value in P11 can be from 10 to 80 based on that statment. I have tried
gt; to add in the front of the IF statment quot;IF(M11=0,0,quot; making it like this
gt;
=IF(M11=0,0,IF(M11=90,10,IF(M11=100,20,IF(M11=110, 30,IF(M11=120,40,IF(M11=13
0,50,IF(M11=140,60,IF(M11=150,70,IF(M11gt;159,80,))) ))))))
gt; and it always fails with an error. What am I doing wrong?
The problem is that Excel permits up to 7 level of nesting
Generally one use a lookup formula (see Help on VLOOKUP) when an IF cannot
accommodate all the choices
But in your case you could use =IF(M11=0,0,IF(M11gt;159,80,M11-80))
or =IF(M11=0,0,MAX(80,M11-80))
Not clear what you want when M11 is , for example, 145.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;BThrasherquot; gt; wrote in message
news
gt;I have the folowing IF statment that works well.
gt;
gt; in quot;P11quot;
gt; =IF(M11=90,10,IF(M11=100,20,IF(M11=110,30,IF(M11=1 20,40,IF(M11=130,50,IF(M11=140,60,IF(M11=150,70,IF (M11gt;159,80,))))))))
gt;
gt; The value in P11 can be from 10 to 80 based on that statment. I have tried
gt; to add in the front of the IF statment quot;IF(M11=0,0,quot; making it like this
gt; =IF(M11=0,0,IF(M11=90,10,IF(M11=100,20,IF(M11=110, 30,IF(M11=120,40,IF(M11=130,50,IF(M11=140,60,IF(M1 1=150,70,IF(M11gt;159,80,)))))))))
gt; and it always fails with an error. What am I doing wrong?
Thanks I did not know it was limited to 7 deep.
bob

quot;Bondiquot; wrote:

gt; Hi,
gt;
gt; This might help:
gt;
gt; www.cpearson.com/excel/nested.htm
gt;
gt; Regards,
gt; Bondi
gt;
gt;