Sliding Scale Commission Table

[dsmgdesign]

I need a Sliding Scale Commission table so I can figure out how to pay out based on the price. I've set this up as 3 tiers, but there could be 4. This is the closest thread I could find on the topic, but I couldn't get this to work for my situation: Sliding Commission Scale (I know its been asked before!)

What would be best is if I can just plug in the numbers that apply to me (mix, max, and commission). And, then when I plug in the actual price, the payout is calculated based on the sliding scale in the table that I created. Something like the below. I just need the formulas to make this work.

	Min	Max	Commission
Tier 1	$XXX.XX	$XXXX.XX	XX%
Tier 2	$XXXX.XX	$XXXX.XX	XX%
Tier 3	$XXXX.XX	$XXXX.XX	XX%
Price	Payout
$XXXX.XX	$XXX.XX

 

[etaf]

how is the tier worked - some real numbers and examples would help

Lets assume T1 = 100 to 200 T2 = 201 - 300 and T3 = 301+
if they do 500 say
do they get nothing for the 1st 100 in my example
400
Then for the next 100 , they get A%
Then for the next 100 they get B%
and then for the last 200 they get C%
or is it 500 * C%

 

[dsmgdesign]

T1 would start at $0.00. Sorry, I missed that part.

No real numbers, as that would not be useful to anyone else but me. I hate reading threads with confusing formulas that I can't replicate to my situation.

Not as simple as they get X% for $Y. I wish it were. I'm looking for a sliding scale, kinda like the thread I sent: Sliding Commission Scale (I know its been asked before!) So, you can use that as an example. My numbers are just different than in that post, and I can't figure out how to make it work.

 

[etaf]

No real numbers, as that would not be useful to anyone else but me.

that is what the sumproduct uses the numbers
=SUMPRODUCT((A1>{80000,130000,295000,495000})*(A1-{80000,130000,295000,495000})*{0.3,0.05,0.05,0.1})
This uses the thresholds, so the actual numbers and the % values

Cumulative Tier Commission Formula on excel

Good Evening, Was hoping someone could please help me. I am trying to create a formula to calculate the commission as below 495,001 = 50% so based on person making 500,000 total for the quarter, his bonus will be per below 14,999.7+57,749.65+79,999.6 + 2,499.5 total = 155,248.45 can you...

www.mrexcel.com

if you setup your table like the following - then a sumproduct should work OK
BUT i have had to make up numbers as you seem reluctant to show yours and check the results are as you expect
ANYWAY

Table and IF.xlsx
	A	B	C	D	E	F	G
21
22		Amount		Commission		Amount	Payout
23		0	5%	5%		600	100
24		101	10%	5%
25		201	15%	5%
26		301	20%	5%
27		401	25%	5%
Sheet1

Cell Formulas
Range	Formula
G23	G23	=SUMPRODUCT(--(F23>B23:B27),F23-B23:B27,D23:D27)
D23	D23	=C23
D24:D27	D24	=C24-C23

 

[AhoyNC]

Here is a link that explains the formula, that may be of help.

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

 

[dsmgdesign]

I tried this out, and it seems to work. My numbers are in reverse order (smaller $ amounts get bigger %'s and then scales down from there the higher the $ amount), and the calculation still seems to work. I added a max amount column, and hid your column D so it was more user friendly. Thank you!

 

[etaf]

glad it worked out for you ok