Formula for Calculating Cumulative Commission

[Laura Moylan]

I need a formula for calculating the commission structure below. I am struggling with having the calculation recognize and calculate only if commission is accrued, since the first $5500 does not attract commission. Then once within the commission tiers, calculating only on the amounts within that specific tier at the percentage for that tier.

The commission tier structure is:
Tier 1: $0-$5,500.00 = 0%
Tier 2: $5,501.00-$9,000.00 = 10%
Tier 3: $9,001.00 and above= 12%

As an example of what I mean, let's use sales figure of $8,500.00. The total commission for this sales figure should equal $300.00 (10% of $3,000.00 which is the difference between $8,500.00 and $5,500.00, known as Tier 2,)

If the sales figure was changed to $11,000.00, the commission payable would become $590.00 (the sum of 10% of $3,500.00 - which is the difference between $9,000.00 and $5,500.00, known as Tier 2; and 12% of $2,000.00 - which is the difference between $11,000.00 and $9,000.00, which is now into Tier 3)

Hopefully I've made sense.
Please help!!

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	11,000.00	590.00			Bracket	Rate	Rate Delta
2		590.00			0.00	0%	0%
3					5,500.01	10%	10%
4					9,000.01	12%	2%
7a

Cell Formulas
Range	Formula
B1	= SUMPRODUCT(--(A1>rB),A1-rB,rDelta)
B2	=(A1>5500)*(A1-5500)*0.1+(A1>9000)*(A1-9000)*0.02
G2	=F2
G3	=F3-F2
G4	=F4-F3

Named Ranges
Name	Refers To	Cells
rB	='7a'!$E$2:$E$4
rDelta	='7a'!$G$2:$G$4

The above shows two alternatives for your consideration.

 

[Laura Moylan]

Hi Dave

The formula : =(A1>5500)*(A1-5500)*0.1+(A1>9000)*(A1-9000)*0.02
works perfectly. However, I want to add editable cells for the tier values and am having trouble with it. I thought I'd be able to do something like this, but it is not returning any value:

=(A1>A10)*(A1-A10)*B10+(A1>A11)*(A1-A11)*B11

For this example, the values represented are:
A10: 5500
A11: 9000
B10: 0.1
B11: 0.02

Can you help?

 

[Laura Moylan]

Sorry I should clarify that the B values work fine when the number is substituted with the cell.

Its the A10 and A11 cells which seem to be the problem.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	11,000.00	590.00			Bracket	Rate	Rate Delta
2		590.00			0.00	0%	0%
3		590.00			5,500.00	10%	10%
4					9,000.00	12%	2%
7a

Cell Formulas
Range	Formula
B3	=(A1>$E$3)*(A1-$E$3)*$F$3+(A1>$E$4)*(A1-$E$4)*($F$4-$F$3)

 

[Dave Patton]

To review the logic of a formula or to check a formula, you can try Excel's Evaluate Formula.