Excel Nested If Formula Error - ANDIF

Question

Basically, if the value to be inputted (F33) is upto the amount in column A, it gets multiplied by the rate(%) next to it. However, this changes when the value is above 60k; there are two possibilities after 60k (which I inputted into the formula below. However, excel keeps giving me an error and I do not know what the error is? Can anyone spot it? I would be grateful, it has been bugging me for a hours now. Should I try to put formulas in different cells for values above 60k and then 'call' those cells, instead of putting them directly into the formula? I am not really sure what is failing this argument: A40: 11000, A41: 27,000, A42: 60000, A43: 97000

'=if(AND($F$33>0,$F$33<$A$40),$F$33*B40,IF(AND($F$33>$A$40,$F$33<$A$41),$F$33*$B$41,IF(AND($F$33>$A$41,$F$33<$A$42),$F$33*$B$42,IF(AND($F$33>$A$42,$F$33<$A$43),(13760+(($F$33-60000)*0.35)),IF(AND($F$33>$A$43,(23750+(($F$33-97000)*0.35))))))))

score 0 · Accepted Answer · answered Nov 29 '14 at 15:45

In the final IF, you have missed a closing bracket on the condition and there is no ELSE value.

Here is your test (corrected) set out as a tree :

=if(AND($F$33>0,$F$33<$A$40),
    $F$33*B40,
    IF(AND($F$33>$A$40,$F$33<$A$41),
        $F$33*$B$41,
            IF(AND($F$33>$A$41,$F$33<$A$42),
                $F$33*$B$42,
                    IF(AND($F$33>$A$42,$F$33<$A$43),
                        (13760+(($F$33-60000)*0.35)),
                            IF(AND($F$33>$A$43),
                                (23750+(($F$33-97000)*0.35)),
                                #N/A
                            )
                    )
            )
    )
)

I have used #N/A where the formula returns no value. If this is never the case, you can remove the last IF. It's useful to do this for very complex conditions and with an editor which highlights the matching brackets.

Here it is on one line :

=if(AND($F$33>0,$F$33<$A$40),$F$33*B40,IF(AND($F$33>$A$40,$F$33<$A$41),$F$33*$B$41,IF(AND($F$33>$A$41,$F$33<$A$42),$F$33*$B$42,IF(AND($F$33>$A$42,$F$33<$A$43),(13760+(($F$33-60000)*0.35)),IF(AND($F$33>$A$43),(23750+(($F$33-97000)*0.35)),#N/A)))))

Thank you very much! Now got rid of the last And as well, as it was not necessary. I see what you mean with the last IF, good point! — Mete, Nov 29 '14 at 16:05

Excel Nested If Formula Error - ANDIF

1 Answers1