I made a spreadsheet that calculates insurance rates based on employer contribution % towards the employee and dependent. The entire formula works perfectly except the combination of 100% employer contribution for employee and >0.1%, which is the last section of the formula below. I attempted to cover all possible combinations:
Employer Contribution
Employee Dependent
0% 0%
100% 0%
100% 100%
1%-99% 0%
1%-99% 1-99%
100% 1-99% - This combination produces a "FALSE" in the formula below
Here is how the spreadsheet is laid out:
Employer Contribution
Employee:100% (Cell A1) Dependent:0% (Cell A2)- Enter any percentage for each
Below are hard keyed numbers
(Cell A4) (Cell B4) (Cell C4)(Cell D4)(Cell E4)
Name Employee Spouse Children Total Name Employer Employee Total
Johnson $200 $200 $100 $500 Johnson POPULATED BASED ON THE FORMULA
Here is the formula:
= IF(AND(A1=0%,0,
IF(AND(A1=100%,A2=0%),B4,
IF(AND(A1=100%,A2=100%),E4,
IF(AND(A1>0.1%,A1<100%,A2>0.1%,A2<100%),(SUM(C4:D4)*A2)+(B4*A1),
IF(AND(A1=100%,A2>0.1%,A2<100%),(SUM(C4:D4)*A2)+(A1*A4))))))) - this line doesnt work
I am looking to troubleshoot why the last combination does not work. Thank you for the help!
Aucun commentaire:
Enregistrer un commentaire