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))>

 

[MartinBecks]

=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,
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, "")))))

Here's the formula in it's entirety. All it needed was the spaces.

 

[jasonb75]

That was the logic I was working to, lucky guess

Just looking over my suggested formula, I noticed that I had U64 where it should have been P72, I've corrected that now and refined it a little more.

I've been testing this with random values and it's giving the same results as your original formula would.

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

edit:-

Yes, that is the entire formula, nothing got cut off on this one.

 

[jasonb75]

Going back to your original post where you mentioned #value errors from your formula.

I can see no reason for your formula to fail, (U64*0.5)=P72 would return a blank, same for Q64, but no reason for the error.

Given that you say it returns the error instead of a blank, or in the last 2 if statements, I would assume that the first 2 statements return values as expected.

Based on that, the only reason I can see for the error would be a non-numeric value in Q64. Until this is corrected all formula will fail at the same point, although mine will return a blank instead of an error.