Nested IF, parenthesis

[aiki100]

Hello -


Excel is telling me that I have an error with this formula. Does anyone have an idea of what it might be, and why?


Thank you very much!


=IF(AV3="C",K3,IF(AV3="O",H4,IF(AND(AZ3="L",AV3="B",I4>=I3+$B$2),I3+$B$2,IF(AND(AZ3="S",AV3="B",J4<=J3-$B$2),J3-$B$2,IF(AND(AZ3="L",AV3="BA",I4>=I3),I3,IF(AND(AZ3="S",AV3="BA",J4<=J3),J3,IF(AND(AZ3="L",AV3="%",I4>=I3+(I3*$B$3),I3+(I3*$B$3),IF(AND(AZ3="S",AV3="%",J4<=J3-(J3*$B$3),J3-(J3*$B$3),"")))))))))

 

[AhoyNC]

Re: Nested IF, parenthesis, help please, thanks!

You didn't close off 2 of the AND statements.
Try:

=IF(AV3="C",K3,IF(AV3="O",H4,IF(AND(AZ3="L",AV3="B",I4>=I3+$B$2),I3+$B$2,IF(AND(AZ3="S",AV3="B",J4<=J3-$B$2),J3-$B$2,IF(AND(AZ3="L",AV3="BA",I4>=I3),I3,IF(AND(AZ3="S",AV3="BA",J4<=J3),J3,IF(AND(AZ3="L",AV3="%",I4>=I3+(I3*$B$3),I3+(I3*$B$3)),IF(AND(AZ3="S",AV3="%",J4<=J3-(J3*$B$3),J3-(J3*$B$3)),""))))))))

 

[aiki100]

Re: Nested IF, parenthesis, help please, thanks!

First off, thank you very much! it is very kind of you to straighten me out. I did look at that, and knew it must be an issue with closing something somewhere, but just couldn't see it.

Two questions please:

A- I am now returning a FALSE from the formula, even though I have contrived data that would otherwise show a numeric return. Additionally, I have now changed some of the formula (but the FALSE predates any changes I made to the formula). Might you have any thoughts on this, please?

[FONT=&quot]=IF(AV3="C",K3,IF(AV3="O",H4,IF(AND(AZ3="L",AV3="B",I4>=I3+$B$2),I3+$B$2,IF(AND(AZ3="S",AV3="B",J4<=J3-$B$2),J3-$B$2,IF(AND(AZ3="L",AV3="BA",I4>=I3),I3,IF(AND(AZ3="S",AV3="BA",J4<=J3),J3,IF(AND(AZ3="L",AV3="%",I4>=I3+((I3-J3)*($B$3)),I3+((I3-J3)*$B$3)),IF(AND(AZ3="S",AV3="%",J4<=J3-(I3-J3)*($B$3),J3-(I3-J3)*$B$3),""))))))))[/FONT]


B - Since I did indeed look at this thing (for quite a while, too...lol), I am wondering if you might be willing to share your thought process on how you broke that down to find where my error was? I usually do just that, break it apart and look at it statement by statement, but clearly, I still missed it, and I am betting there are better ways to investigate and proceed to resolve error in my syntax.

Thank you again very much!

[FONT=&quot]
[/FONT]

 

[AhoyNC]

Re: Nested IF, parenthesis, help please, thanks!

In answer to your part B.
When you enter a function Excel shows a box with the different arguments. In this case I just put the cursor after each "," in your formula to see what argument showed in bold. I found that were you had started a new IF function it was asking for a logical statement in the AND function.

Has far as question A. I would build your formula one IF statement at a time and see what answers you get with each new IF statement, that should help narrow down were the issue is. It might just be the order you have your IF statements in.

 

[aiki100]

Re: Nested IF, parenthesis, help please, thanks!

AhoyNC -

Thank you for responding - that is the way I have approached it in the past as well. I do think I sometimes get lost in the Parenthesis at the end of a deep set of nested ifs. I'll work on it.

And you may be onto something with the order of AND statements being the issue. I put this together in the interim, and it resolves fine.

[FONT=&quot]=IF(I4>= I3+(I3-J3)*$B$3, I3+(I3-J3)*$B$3,"")

But this one, with the AND, does not:

[/FONT]
[FONT=&quot]=IF(AND(AV3="%",I4>(I3+((I3-J3)*($B$3))),(I3+((I3-J3)*($B$3)))),"")

Weird. I think is should work...hmmmm

Anyway - I will try rearranging the statements per your suggestion, unless you notice something here.


Thanks again for your help, it is much appreciated![/FONT]

 

[jtakw]

Re: Nested IF, parenthesis, help please, thanks!

Hi,

This is Only correcting your formula:

=IF(AND(AV3="%",I4>I3+((I3-J3)*($B$3))),I3+((I3-J3)*($B$3)),"")

 

[AhoyNC]

Re: Nested IF, parenthesis, help please, thanks!

Something looks off in the 3rd logical statement of your AND function, shouldn't it be equal to, greater or less than something?

[TABLE="width: 401"]
<colgroup><col width="401"></colgroup><tbody>[TR]
   [TD="width: 401"]AND(AV3="%",I4>(I3+((I3-J3)*($B$3))),[COLOR=#ff0000](I3+((I3-J3)*($B$3)))[/COLOR])
[/TD]
 [/TR]
</tbody>[/TABLE]

 

[jtakw]

Re: Nested IF, parenthesis, help please, thanks!

Something looks off in the 3rd logical statement of your AND function, shouldn't it be equal to, greater or less than something?

[TABLE="width: 401"]
<tbody>[TR]
[TD="width: 401"]AND(AV3="%",I4>(I3+((I3-J3)*($B$3))),[COLOR=#ff0000](I3+((I3-J3)*($B$3)))[/COLOR])
[/TD]
[/TR]
</tbody>[/TABLE]

I think that's supposed to be the "Value if True" part of the OP's IF formula, similar in structure of the other formula in the same Post (#5).

 

[aiki100]

Re: Nested IF, parenthesis, help please, thanks!

Thank you both for your thoughts- I greatly appreciate your time in taking a look at this. I wish you a very good weekend!

 

[aiki100]

Re: Nested IF, parenthesis, help please, thanks!

I definitely see what I missed here. Wow. How could I miss that? Not enough coffee? Too much coffee?

Anyway, that cleaned it up perfectly!

Thank you jtakw! I really appreciate your help in this...Have a nice weekend!