Commission formula help

[ksavagecc]

I need a formula to calculate tiered commissions based on incremental sale numbers. For example, .5% for 0-25, .65% for 26-49, .75% for 50-99, .9% for 100-149, 1.0% for >150. Each sale commission is based on total value of each individual sale.

 

[Smitty]

Welcome to the Board!

I'd suggest a LOOKUP table.

Here's an example.

And you can use the Scenario Manager if you have to also calculate what's being sold.

HTH,

 

[shg]

If A1 contains the quantity,

=SUMPRODUCT( ($A9 > {0;26;50;100;150}) * ($A9 - {0;26;50;100;150}) * {0.5;0.15;0.1;0.15;0.1}% )

returns the percentage (format the result as 0.0%)

{0;26;50;100;150} are the breakpoints, and

{0.5;0.15;0.1;0.15;0.1} are the differences between adjacent rates.

EDIT: I think I misunderstood the problem, sorry. Maybe

=LOOKUP(A9, {0;26;50;100;150}, {0.5;0.65;0.75;0.9;1}% )

 

[scottylad2]

Set up a Vlookup table like this

Excel Workbook
	G	H	I	J
14	Sales	Commision	0	0.5
15	26	0.65	26	0.65
16			50	0.75
17			100	0.9
18			150	1
Sheet1

 

[ksavagecc]

Thank you for the quick reply. the post was my first ever and I left some information out. I am using 2010 Excel and the answer I need is the commission to be paid. Column N has the value of each sale listed in it.

 

[shg]

Maybe

=N1 * LOOKUP(N1, {0;26;50;100;150}, {0.5;0.65;0.75;0.9;1}% )

 

[scottylad2]

Still working with a lookup table, and your very generous comission rates

Excel Workbook
	L	M	N	O	P
1	Salesperson	Total Sales	Comission to be paid	Commision Table
2	Salesman 1	58	43.5	0	0.5
3	Salesman 2	78	58.5	26	0.65
4	Salesman 3	116	104.4	50	0.75
5	Salesman 4	242	242	100	0.9
6	Salesman 5	226	226	150	1
7	Salesman 6	242	242
8	Salesman 7	37	24.05
9	Salesman 8	189	189
10	Salesman 9	119	107.1
11	Salesman 10	128	115.2
Sheet1

 

[ksavagecc]

Thanks! It worked perfectly.