vendredi 9 février 2018

Excel Nested If - Calculating Commission

I need to calculate the sales commission depending on the sales achievement.

AMOUNT       PERCENT             NOTE
8000-9999        5%            achievement*.05
10000-11999     20%            difference of achievement-8000*.2+400
12000-13999     25%            difference of achievement-8000*.25+400
14000-15999     30%            difference of achievement-8000*.30+400
16000 & ABOVE   35%            difference of achievement-8000*.35+400

I have this in excel formula,

=IF(H3>8000,(H3*0.05),IF(H3<9999,H3*0.05,IF(H3>10000,((H3-8000)*0.2+400),IF(H3<11999,((H3-8000)*0.2+400),IF(H3>12000,((H3-8000)*0.25+400),IF(H3<13999,((H3-8000)*0.25+400),IF(H3>14000,((H3-8000)*0.3+400),IF(H3<15999,((H3-8000)*0.3+400),IF(H3>16000,((H3-8000)*0.35+400))))))))))

I tested with

Achieved Amount         Commission
8724                    436.2        //correct
10000                   500          //wrong

I believed the other formula was not read. I would appreciate every help.

Thanks!!

Aucun commentaire:

Enregistrer un commentaire