Nested multiple IF, AND & OR is returning #VALUE!

[MartinBecks]

I'm having difficulty executing an IF, AND & OR nested statement. I want to calculate a simple payback that includes an incentive amount. There's a drop-down cell, K64, that has five choices. Depending on what is selected it either references dollar amounts in either Q64 or U64 and compares it to the savings P72. Then depending on the selections and comparisons it should calculate the simple payback. The first three IF statements work great but I'm still having issues with the last two.

The 2 issues are:
1. When nothing is selected in the drop-down cell, K64, the formula currently returns #VALUE! instead of a blank cell.
2. I can't get the last two IF statements to calculate a value. When I try, it returns a #VALUE!.

It's in PHP code because plain text kept deleting 1/2 of the IF statements.

<p72,or(k64=k$181,k64=k$182,k64=k$183)),(u64-(u64*0.5)) p72,
<p72, k64="K$185)," (q64-(q64*0.5))="" p72,
<p72,or(k64=k$181,k64=k$182,k64=k$183)),(u64-(u64*0.5)) p72,
<p72, k64="K$185)," (q64-(q64*0.5))="" p72,

=IF(K64=K$184,"Insert #",IF(AND(U64*0.5P72, OR(K64=K$181,K64=K$182,K64=K$183)),(U64-P72)/P72,IF(AND((Q64*0.5)P72, K64=K$185), (Q64-(Q64-P72))/P72, "")))))

I feel so close but I can't find my error. Please help. Your comments are appreciated.
Using Excel 2010</p72,></p72,or(k64=k$181,k64=k$182,k64=k$183)),(u64-(u64*0.5))></p72,></p72,or(k64=k$181,k64=k$182,k64=k$183)),(u64-(u64*0.5))></p72,></p72,or(k64=k$181,k64=k$182,k64=k$183)),(u64-(u64*0.5))>

 

[jasonb75]

It looks like there is still some of your formula missing.

If you preview your post, switch from standard to advanced editor, or edit your post after submission then the formula will still corrupt even with php tags.

 

[Redwolfx]

This?

=IF(K64=K$184,"Insert #",IF(AND(U64*0.5=P72,OR(K64=K$181,K64=K$182,K64=K$183)),(U64-P72)/P72,IF(AND((Q64*0.5)*P72,K64=K$185),(Q64-(Q64-P72))/P72,"")))

 

[Joe4]

Welcome to the Board!

I think you are still having issues with your formula, because this part certainly doesn't look right:

[COLOR=#0000BB][FONT=monospace]U64[/FONT][/COLOR][COLOR=#007700][FONT=monospace]*[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]0.5P72[/FONT][/COLOR]

Note, if you just include the regular code tags around your formula, it should maintain your entire formula.

I am guessing that the issue with posting the formula is that you have a less than sign in your formula, which is being interpretted as HTML code. You can also get around that issue by placing spaces on both sides of it, i.e. " < ".

However, it might just be just as well to list out all your conditions, in the order they appear, and let us take a crack at the formula ourselves (it can be tough to decipher someone else's formula if all the logic isn't quite right).

 

[MartinBecks]

I'm having issues posting the whole formula. It keeps getting chopped into bits.

This is frustrating. I'm unable to post the formula without it falling completely apart. I'll try posting it in bits.

=IF(K64=K$184,"Insert #",
IF(AND(U64*0.5<p72,or(k64=k$181,k64=k$182,k64=k$183)),(u64-(u64*0.5)) p72,
IF(AND(U64*0.5>P72, OR(K64=K$181,K64=K$182,K64=K$183)),(U64-P72)/P72,


the first two IFs. </p72,or(k64=k$181,k64=k$182,k64=k$183)),(u64-(u64*0.5))>

 

[Redwolfx]

Try Any time you have a > or < put a space after it.

 

[MartinBecks]

the next line....

IF(AND(U64*0.5>P72, OR(K64=K$181,K64=K$182,K64=K$183)),(U64-P72)/P72,

another line..

IF(AND((Q64*0.5) less than P72, K64=K$185), (Q64-(Q64*0.5))/P72,
<p72, k64="K$185)," (q64-(q64*0.5))="" p72,

the final line is...

IF(AND((Q64*0.5)>P72, K64=K$185), (Q64-(Q64-P72))/P72, "")))))



the actual formula has a < not the written "less than" this was the roundabout way it allowed me to post the whole formula.

</p72,>

 

[jasonb75]

Martin, can you check the post with the "first 2 if's", it looks like that one broke again. As Joe4 and redwolf have both suggested, insert spaces before and after < or > then you can post the entire formula with no errors.

 

[Redwolfx]

This?

=IF(K64=K$184,"Insert #",IF(AND(U64*0.5> P72, OR(K64=K$181,K64=K$182,K64=K$183)),(U64-P72)/P72,IF(AND((Q64*0.5) < P72, K64=K$185), (Q64-(Q64*0.5))/P72,IF(AND((Q64*0.5)> P72, K64=K$185), (Q64-(Q64-P72))/P72, ""))))

 

[jasonb75]

I've corrected the parts that didn't look right by applying the same logic used in the rest of the formula, I think Red has done the same.

One observation, your formula allows for < P72 or > P72, but what if the value is = P72?

Maybe

=IFERROR(CHOOSE(MIN(MATCH(K64,K$181:K$185,0)-2,1),IF((U64*0.5) < P72,(U64*0.5)/U64,(U64-P72)/P72),"Insert #",IF((Q64*0.5) < P72, (Q64-(Q64*0.5))/P72, (Q64-(Q64-P72))/P72)),"")