Nested if statements

[jfrey]

This is my statement

=if(D10<=250,1,If(D10<=500,2,if(D10<=750,3,if(D10<=1000,4,if(D10<=1250,5,if(D10<=1500,6,if(D10<1750,7,if(D10<=2000,8,if(D10<=2250,9,if(D10<=2500,10,if(D10<=2750,11,if(D10<=3000,12,if(D10<=3250,13,if(D10<=3500,14,NA))))))))))))))

is excel limited on the number of nested if statements? if so how do i get around this? It's saying i can only have 8.

[Miraun]

Isn't that equation just Roundup(D10/250,0)
?

[Miraun]

But, regardless, to remove imbedded IF statements in the future, you can change it so that each condition is a distinct IF.

For example:
IF(D10<=3500,1,0)+IF(D10<=3250,1,0) etc, etc... so that you'd just be adding 1 for each additional 250 or whatever break points you want...

Alternatively, you may want to look into the SUMPRODUCT command if you have a variety of different breakpoints and changes.

[veejar]

Hi,

Try this formula Instead
"Formula: =IF(AND(A3>0,A3<=250),1,IF(AND(A3>250,A3<=500),2,IF(AND(A3>500,A3<=1000),3,IF(AND(A3>1000,A3<=1500),4,"NA"))))"

Regards,

Veejar

[jfrey]

that would only let me do 7 statements. I need a scale from 250 to 4500, incraments of 250.

[arthurbr]

What is wrong with the suggestion in post #2 ? =roundup(d10/250,0)

[jfrey]

I wasn't sure how to use the roundup function.

That worked. I guess there is more than one way to skin a cat.

Thanks guys!