What is wrong with this IF statement? need help.

This statement works fine but I want the cell to say 0" if the cell H6 has a
zero in it. As the stetments stands right now it displayes 6" in the cell if
H6 is 0. How can I make that happen?

=IF(H6<101,"6""",IF(AND(H6>101.1,H6<151),"7""",IF(AND(H6>151.1,H6<201),"8""",IF(AND(H6>200.1,H6<275.1),"9""",IF(AND(H6>275.2,H6<350.1),"10""",IF(AND(H6>350.2,H6<601),"12""",IF(AND(H6>601.1,H6<901),"14""")))))))

Please help me correct this statment. Thank you.

You're approaching the limit of nested functions. But since you're working with
text, you could just concatenate your strings:

=IF(H6=0,"0""","")
&IF(AND(H6>0,H6<101),"6""","")
&IF(AND(H6>101.1,H6<151),"7""","")
&IF(AND(H6>151.1,H6<201),"8""","")
&IF(AND(H6>200.1,H6<275.1),"9""","")
&IF(AND(H6>275.2,H6<350.1),"10""","")
&IF(AND(H6>350.2,H6<601),"12""","")
&IF(AND(H6>601.1,H6<901),"14""","")

But what happens at 101, 151, 201, ...?

I would think you'd want <= or >= in a few of those statements.



AC man wrote:
>
> This statement works fine but I want the cell to say 0" if the cell H6 has a
> zero in it. As the stetments stands right now it displayes 6" in the cell if
> H6 is 0. How can I make that happen?
>
> =IF(H6<101,"6""",IF(AND(H6>101.1,H6<151),"7""",IF(AND(H6>151.1,H6<201),"8""",IF(AND(H6>200.1,H6<275.1),"9""",IF(AND(H6>275.2,H6<350.1),"10""",IF(AND(H6>350.2,H6<601),"12""",IF(AND(H6>601.1,H6<901),"14""")))))))
>
> Please help me correct this statment. Thank you.

--

Dave Peterson

I didn't notice the 101.1. But I'd still use:
&IF(AND(H6>=101,H6<151),"7""","")

for things like: 101.00001




Dave Peterson wrote:
>
> You're approaching the limit of nested functions. But since you're working with
> text, you could just concatenate your strings:
>
> =IF(H6=0,"0""","")
> &IF(AND(H6>0,H6<101),"6""","")
> &IF(AND(H6>101.1,H6<151),"7""","")
> &IF(AND(H6>151.1,H6<201),"8""","")
> &IF(AND(H6>200.1,H6<275.1),"9""","")
> &IF(AND(H6>275.2,H6<350.1),"10""","")
> &IF(AND(H6>350.2,H6<601),"12""","")
> &IF(AND(H6>601.1,H6<901),"14""","")
>
> But what happens at 101, 151, 201, ...?
>
> I would think you'd want <= or >= in a few of those statements.
>
> AC man wrote:
> >
> > This statement works fine but I want the cell to say 0" if the cell H6 has a
> > zero in it. As the stetments stands right now it displayes 6" in the cell if
> > H6 is 0. How can I make that happen?
> >
> > =IF(H6<101,"6""",IF(AND(H6>101.1,H6<151),"7""",IF(AND(H6>151.1,H6<201),"8""",IF(AND(H6>200.1,H6<275.1),"9""",IF(AND(H6>275.2,H6<350.1),"10""",IF(AND(H6>350.2,H6<601),"12""",IF(AND(H6>601.1,H6<901),"14""")))))))
> >
> > Please help me correct this statment. Thank you.
>
> --
>
> Dave Peterson

--

Dave Peterson

Not sure but I think you reached the max for that formula. Have a look at
this lookup formula instead.
=LOOKUP(H6,{0,.1,101.1,151.1,201.1,275.2,350.2,601.1,901.1},{0,6,7,8,9,10,12,14,14})&CHAR(34)

901.1 and above will result in 14" .....you will need to change it if that
is not the correct result you want by changing the last "14" in the second
bracket.

HTH
JG




"AC man" wrote:

> This statement works fine but I want the cell to say 0" if the cell H6 has a
> zero in it. As the stetments stands right now it displayes 6" in the cell if
> H6 is 0. How can I make that happen?
>
> =IF(H6<101,"6""",IF(AND(H6>101.1,H6<151),"7""",IF(AND(H6>151.1,H6<201),"8""",IF(AND(H6>200.1,H6<275.1),"9""",IF(AND(H6>275.2,H6<350.1),"10""",IF(AND(H6>350.2,H6<601),"12""",IF(AND(H6>601.1,H6<901),"14""")))))))
>
> Please help me correct this statment. Thank you.

=IF(H6=0,0,IF(H6<101,"6""",IF(H6<151,"7""",IF(H6<201,"8""",IF(H6<275.1,"9""",IF(H6<350.1,"10""",IF(H6<601,"12""",IF(H6<901,"14""",0))))))))

Try something like this out, adjust values, as needed ( in your example the
figure 151 would not register as it you have less than 151 and in the next
section you ask if its greater than 151 thus making this number void, you
may have wanted to do this im not sure ?

Pete

--
(][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)
"AC man" <ACman@discussions.microsoft.com> wrote in message
news:F45CC9FE-0F33-4419-BDEB-D55B30A41706@microsoft.com...
> This statement works fine but I want the cell to say 0" if the cell H6 has
> a
> zero in it. As the stetments stands right now it displayes 6" in the cell
> if
> H6 is 0. How can I make that happen?
>
> =IF(H6<101,"6""",IF(AND(H6>101.1,H6<151),"7""",IF(AND(H6>151.1,H6<201),"8""",IF(AND(H6>200.1,H6<275.1),"9""",IF(AND(H6>275.2,H6<350.1),"10""",IF(AND(H6>350.2,H6<601),"12""",IF(AND(H6>601.1,H6<901),"14""")))))))
>
> Please help me correct this statment. Thank you.