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.
<tbody>
</tbody>
Here is how my spreadsheet looks:
<tbody>
</tbody>
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:
Employer Contribution % | Employer Contribution % |
Employee | Dependent |
0% | 0% |
100% | 0% |
100% | 100% |
1%-99% | 0% |
1%-99% | 1%-99% |
100% | 1-99% |
<tbody>
</tbody>
Here is how my spreadsheet looks:
Employer Contribution % | |||||||||
Employee % (Cell A1) | |||||||||
Dependent % (Cell A2) | |||||||||
(Cell A4) | (Cell B4) | (Cell C4) | (Cell D4) | (Cel E4) | (Cell H4) | (Cell I4) | (Cel J4) | ||
Name | Employee | Spouse | Children | Total | Employer | Employee | Total | ||
Johnson | $100 | $100 | $100 | $300 | IF(AND | ||||
<tbody>
</tbody>
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:
<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>