Multiple nested IF(AND formula troubleshooting

[Stuepef]

I created a auto-calculate spreadsheet to display Employer contribution % for both employees and dependents (enter % in specific cell). To account for the many combinations of 0%-100%, I have tried to encompass the following combinations in a nested IF(AND statement. I am looking to put different %'s in cells A1 & A2 to calculate how much the employer and the employee have to pay.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employer Contribution %[/TD]
[TD]Employer Contribution %[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[TD]Dependent[/TD]
[/TR]
[TR]
[TD]0%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]100%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]1%-99%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]1%-99%[/TD]
[TD]1%-99%[/TD]
[/TR]
[TR]
[TD]100%[/TD]
[TD]1-99%[/TD]
[/TR]
</tbody>[/TABLE]

Here is how my spreadsheet looks:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employer Contribution %[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee % (Cell A1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dependent % (Cell A2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](Cell A4)[/TD]
[TD](Cell B4)[/TD]
[TD](Cell C4)[/TD]
[TD](Cell D4)[/TD]
[TD](Cel E4)[/TD]
[TD][/TD]
[TD][/TD]
[TD](Cell H4)[/TD]
[TD](Cell I4)[/TD]
[TD](Cel J4)[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Employee[/TD]
[TD]Spouse[/TD]
[TD]Children[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD]Employer[/TD]
[TD]Employee[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Johnson[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD]$300[/TD]
[TD][/TD]
[TD][/TD]
[TD]IF(AND[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My formula works out perfectly, except when I added the last line causing the result to be FALSE. If I separate the last line, than the formula works like it should, but for whatever reason, it doesn't when I attach it to the bigger formula. I am trying to troubleshoot why I am receiving the FALSE answer when I put 100% in A1 and 50% in A2.

Here is my current formula:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">=IF(AND(A1=0%,A2=0%),0,
IF(AND(A1=100%,A2=0%),B4,
IF(AND(A1=100%,A2=100%),E4,
IF(AND(A1>1%,A1<=99%,A2=0%),A1*B4,
IF(AND(A1>1%,A1<=99%,A2>1%,A2<=99%),(SUM(C4:D4)*A2)+(A1*B4,
IF(AND(A1=100%,A2>1%,A2<=99%),(SUM(C4:D4)*A2)+(A1*B4))))))))</code>

 

[t0azt]

You are missing the close parenthesis on the second to last line. IF(AND(A1>1%,A1<=99%,A2>1%,A2<=99%),(SUM(C4:D4)*A2)+(A1*B4),

 

[Stuepef]

That was a copy and paste error on my part within the original post. Any thoughts on why I would be receiving the FALSE answer?

 

[t0azt]

Your last IF statement doesnt have a value if false condition so it defaults into saying FALSE meaning none of the conditions in your IF statements are met. This happened when I copied and pasted the formula as you had it but once I added the parenthesis it gave me 200 when A1=100% and A2=50%. This is the formula as it is in my sheet right now:

=IF(AND(A1=0%,A2=0%),0,IF(AND(A1=100%,A2=0%),B4,IF(AND(A1=100%,A2=100%),E4,IF(AND(A1>1%,A1<=99%,A2=0%),A1*B4,IF(AND(A1>1%,A1<=99%,A2>1%,A2<=99%),(SUM(C4:D4)*A2)+(A1*B4),IF(AND(A1=100%,A2>1%,A2<=99%),(SUM(C4:D4)*A2)+(A1*B4)))))))

The only time this would result in FALSE result is when either A1 or A2 is more than 100% or when A1=0

 

[Eric W]

Maybe I'm missing something, but that whole formula appears to resolve to:

=A1*B4+A2*(C4+D4)

 

[Stuepef]

I simplified this problem in the original post, but am I able to attach my actual workbook? I checked and rechecked the formula to match exactly what you wrote and it still does not work.

 

[Stuepef]

My workbook had the information across three sheets within the workbook, but as soon as I moved the contribution %'s to the calculation sheet, it worked fine. Has anyone else ran into this issue before?

 

[Stuepef]

Yes in essence that is what part of the formula is trying to do. I made the IF(AND statement to account for all combinations 0-100% for both the employee and dependent. Definitely open if there is an easier way!

 

[Eric W]

I suggest that you compare both your nested IF formula with my formula from post 5. Try the same data on both of them and see if you get the same results. I checked each case from the IF formula, and they all are covered. If you find a case where you don't get the expected results, let me know.

As far as moving the contribution percentages to another sheet, keep in mind that if you refer to a cell on a different sheet, you have to list the sheet name with it, e.g.

=A1+1 becomes
=Sheet2!A1+1

 

[Stuepef]

The simple formula you made calculates exactly how it should, thank you for simplifying my over complicated attempt!