commision calculation formula

[Charles Toh]

Dear all,

I/m fresh in excel, i need some help on the formula calculation:-

If a sales person want to claims for the commision. He/she must hit 2 main components for the incentive qualification:-
1) Total Gross sales more than 49.9% of the budget AND
2) Total Net gain more than 50% of the budget

If he/she
achieve 49.9 of target get 1% of the gross sales;
achieve 50%>79.9% get 2% of the gross sales;
achieve 80%>to 100% get 3% of the gross sales;
achieve 101% get 5% of the gross sales


Example A
Budget gross sales= 20,000
Budget Net Gain = 10,000

Actual gross sales = 35,000
Actual Net gain = 12,000 (due to some termination)

Please help me..
Charles

 

[moonfish]

ASSUMING:
- A1 contains budget
- B1 contains gross sales
- C1 contains net sales

put this in D1:
=if(and(b1 > a1*0.499, c1 > a1/2),if(b1/a1 >= 0.5,if(b1/a1 >= 0.8,if(b1/a1 >= 1,0.05,0.03),0.02),0.01),0)*b1

That should work, assuming I made no typoes (don't have Excel at hand right now).