Tiered bonus structure formula

[mattarcland]

Good evening,

I am having (a lot of) trouble attempting to put together a formula that properly calculates my organization's tiered bonus formula. Our managers are paid bonuses according to the following criteria:

Goal: "X" units rented
First Tier: 0-49.99% of goal reached, $0 (if you don't hit at least 50% of your goal you don't receive a bonus)
Second Tier: 50%-99.99% of goal reached, $50 per unit rented ($50 for every unit rented up to 100% of goal)
Third Tier: 100%-119.99% of goal reached, $100 per unit rented ($100 for every unit rented between 100%-119.99% of goal)
Fourth Tier: 120%-and above of goal reached, $150 per unit rented ($150 for every unit rented 120% and above of goal)

A real world example would look like this:

Goal: 20 units rented
Actual results: 27 units rented
Second Tier: 20 units x $50 = $1,000
Third Tier: 3 units x $100 = $300
Fourth Tier: 4 units x $150 = $600
TOTAL: $1900

Any help would be greatly appreciated. I'm incredibly stuck on this one. Thank you for your time!

 

[Eric W]

Welcome to the Board!

I had some difficulty with your boundary conditions, which made it hard to use the standard SUMPRODUCT formula, but this seems to work:

=IF(B2< A2*50%,0,IF(B2<=A2*100%,B2*50,IF(B2< A2*120%,A2*50+(B2-A2)*100,A2*50+INT(A2*19.99%)*100+(B2-INT(A2*119.99%))*150)))

where A2 has the goal, and B2 has the actual value.

 

[Dave Patton]

Try and test the following

Excel 2010
	A	B	C	D
1	Goal	20
2	Actual	27
3	Tiers	0	0	0
4		10	$50	1,000
5		20	$100	300
6		23.998	$150	600
7		1000000
8	Total			$1,900
9
10	Total			$1,900
11
7a

Cell Formulas
Range	Formula
B4	=0.5*B1
B5	=B1
B6	=1.1999*B1
D4	=($B$2>=B4)*MIN($B$2,B5)*C4
D5	=($B$2>=B5)*MIN($B$2-INT(B5),INT(B6)-INT(B5))*C5
D6	=($B$2>=B6)*($B$2-INT(B6))*C6
D8	=SUM(D3:D7)
D10	=($B$2>=0.5*B1)*MIN($B$2,B1)*50+($B$2>=B1)*MIN($B$2-B1,INT(1.1999*B1)-B1)*100+($B$2>=1.1999*B1)*($B$2-INT(1.1999*B1))*150

 

[mattarcland]

Thank you so much, Eric! I implemented and it works flawlessly.

 

[mattarcland]

I really appreciate it, Dave! I tested and it works great.

 

[Eric W]

Glad we could help!