Nested IF formula

[stark76]

Hi
I have a nested IF string that is not calculating, any help would be greatly appreciated.

=IF((AND(B5=AE12,AE13,AE18,AE19,AE25,AE26,E8>=2.251,E8<=2.55)),12,0)

 

[Scott T]

Re: Help with nested IF formula

What are you trying to do? the AND function should have multiple logical tests but the cell references in red are not tested. Do they have true or false in them?

AND(B5=AE12,[COLOR=#FF0000]AE13,AE18,AE19,AE25,AE26[/COLOR],E8>=2.251,E8<=2.55)

 

[Joe4]

Re: Help with nested IF formula

Please give a specific example.
What is in each of those cells that your formula references?
What are you trying to test in all those single cell references (i.e. AE18, etc)?

BTW, you only have one IF, so it isn't really a nested IF.

 

[stark76]

Re: Help with nested IF formula

Hi
I need the formula to check to see if B5 matches the result listed in cells AE12,AE13,AE18,AE19,AE25,AE26 Then if E8 = between 2.251 & 2.55 return a value of 12 if true or 0 if false

 

[Joe4]

Re: Help with nested IF formula

You need to list each one explicitly, i.e.

[COLOR=#333333]=IF(AND(AE12=B5,AE13[/COLOR][COLOR=#333333]=B5[/COLOR][COLOR=#333333],AE18[/COLOR][COLOR=#333333]=B5[/COLOR][COLOR=#333333],AE19[/COLOR][COLOR=#333333]=B5[/COLOR][COLOR=#333333],AE25[/COLOR][COLOR=#333333]=B5[/COLOR][COLOR=#333333],AE26[/COLOR][COLOR=#333333]=B5[/COLOR][COLOR=#333333],E8>=2.251,E8<=2.55),12,0)[/COLOR]

 

[Scott T]

Re: Help with nested IF formula

Try

=IF(AND(B5=AE12,B5=AE13,B5=AE18,B5=AE19,B5=AE25,B5=AE26,E8>=2.251,E8<=2.55),12,0)

 

[stark76]

Re: Help with nested IF formula

Thanks Scott T
I've already tried that combo and whilst it does not show up as an error, it does not display a result either.
I altered the formula to reflect what Joe4 suggested and made no difference.

 

[Joe4]

Re: Help with nested IF formula

Are all your values entered as numbers, or are any entered as text?
Note that is easy to check to see which condition is failing. Just check each one by entering formulas into blank cells, like this:

[COLOR=#333333]=AE12=B5
=AE13=B5
[/COLOR][COLOR=#333333]=AE18[/COLOR][COLOR=#333333]=B5
[/COLOR][COLOR=#333333]=AE19[/COLOR][COLOR=#333333]=B5
[/COLOR][COLOR=#333333]=AE25[/COLOR][COLOR=#333333]=B5
[/COLOR][COLOR=#333333]=AE26[/COLOR][COLOR=#333333]=B5
=E8>=2.251
=E8<=2.55[/COLOR]

Assuming that you are expecting 12 and getting 0, whichever of those formulas return FALSE are your issues.

 

[Scott T]

Re: Help with nested IF formula

You can also click inside the and statement and click in the screen tip to select the augment or manually select it. Then press F9 to evaluate .
When you select the part in red and hit F9

=IF(AND([COLOR=#ff0000]B5=AE12[/COLOR],B5=AE13,B5=AE18,B5=AE19,B5=AE25,B5=AE26,E8>=2.251,E8<=2.55),12,0)

This return will show something like this.

=IF(AND([COLOR=#ff0000]TRUE[/COLOR],B5=AE13,B5=AE18,B5=AE19,B5=AE25,B5=AE26,E8>=2.251,E8<=2.55),12,0)

you can then select the nest test and use F9 again

Be sure to hit ESC so you do not hard code the TRUE or FALSE in the formula.

 

[Joe4]

Re: Help with nested IF formula

Scott,

That is a pretty neat little trick. I honestly had not seen it before.
Thanks for sharing!