Help with tiered commission formula

[reggiestewart]

I am trying to create a formula that will do the following:

If the data entered meets a $12,000 threshold, the employee will earn a $250 bonus

Thereafter the employee will earn an additional $50 for every $1,000 in sales. This formula has no "ceiling"

I am familiar with "IF" formulas, but I'm not sure how to carry this on into infinity....In reality, he'll probably never exceed $25-$30K, but that is still a bulky "IF" formula in the way that I know how to write them.

i.e. If he has $18,320 in sales, he should earn the $250 for hitting the $12,000 threshold + $50 X 6 for exceeding $12,000 threshold by $6,000. (There is no provision for any amount between $x,000 and $y,000, if that makes sense)

Does this make sense? Any help would be greatly appreciated!!

 

[Eric W]

Welcome to the forum!

How about:

Book1
	A	B
1	Amount	Bonus
2	5000	0
3	12000	250
4	12500	250
5	13500	300
6	21444	700
Sheet6

Cell Formulas
Range	Formula
B2:B6	B2	=IF(A2>=12000,250+INT(MAX(0,A2-12000)/1000)*50,0)

 

[reggiestewart]

BEAUTIFUL!! Thank you so much! I will both use this and reverse engineer it so I can learn from it.

Take care,

Reggie

Welcome to the forum!

How about:

Book1
	A	B
1	Amount	Bonus
2	5000	0
3	12000	250
4	12500	250
5	13500	300
6	21444	700
Sheet6

Cell Formulas
Range	Formula
B2:B6	B2	=IF(A2>=12000,250+INT(MAX(0,A2-12000)/1000)*50,0)

 

[Eric W]

Happy to help! Let me know if you have any questions.