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>

 

[Eric W]

Glad to help! It does make sense to separate out cases for ease of understanding, but oftentimes you can take an extra step and see if any of the cases can be combined. In some situations you'd still want to keep them separate to see better what's going on, but this time, I think the shorter formula works out pretty well.

Good luck!