mercredi 25 octobre 2017

Multiple IF(AND statements combination troubleshooting

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