Commission Structure

[Jim Shaw]

Hi,

Is it possible to work out my commission?

Here is the table:

£ % £ per bracket Combined Commission
0-2000 5 100 100
2000 – 5000 10 300 400
5000 – 10000 15 750 1150
1000 – 15000 17 850 2000
15000 – 20000 20 1000 3000
20000+ 22

So if I were to bill £14,000, I would get 5% of £2000, 10% of £5000,
15% of £10000 and 17% of £4000 = commission of £1830.

I have an UGLY nested IF fomula, but there MUST be an easier way?

Thanks,

Jim

 

[Bernie Deitrick]

Jim,

In a big ugly formula, without IFs, for the value 14000 in cell A1

=MAX(MIN(0.05*A1,100),0)+MAX(MIN(0.1*(A1-2000),300),0)+MAX(MIN(0.15*(A1-5000
),750),0)+MAX(MIN(0.17*(A1-10000),850),0)+MAX(MIN(0.2*(A1-15000),1000),0)+MA
X(0.22*(A1-20000),0)

For a simpler formula, if you have this table in C1:E7:

Level Percent Commission
2000 0.05 100
5000 0.1 300
10000 0.15 750
15000 0.17 850
20000 0.2 1000
0.22

You can then use this array formula (entered with Ctrl-Shift-Enter), again
with the 14000 in cell A1:

=SUM(IF(A1>C2:C7,E2:E7,IF(A1-C1:C6>0,(A1-C1:C6)*D27,0)))

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

Look here:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Jim Shaw]

Thanks, that is MUCH cleaner.

Can you explain how it works?

And can it give a figure if the billings are less than 2000 or more than 22000?

Thanks,

Jim

 

[Bernie Deitrick]

Jim,

I didn't fully test it, so as given it doesn't work for less than 2000 or
more than 20000. To include those values enter this table in cells C1 to
E8:

Level Percent Commission
0 0 0
2000 0.05 100
5000 0.1 300
10000 0.15 750
15000 0.17 850
20000 0.2 1000
1.00E+9 0.22 0

Then array enter (with Ctrl-Shift-Enter)

=SUM(IF(A1>C2:C8,E2:E8,IF(A1-C1:C7>0,(A1-C1:C7)*D28,0)))

HTH,
Bernie
MS Excel MVP

 

[Jim Shaw]

That is absolutely perfect.

I had this:

=IF(A2<2000,A2*0.05,IF(A2<5000,100+(A2-2000)*0.1,IF(A2<10000,400+(A2-5000)*0.15,IF(A2<15000,1150+(A2-10000)*0.17,IF(A2<20000,2000+(A2-15000)*0.2,IF(A2>2000,3000+(A2-20000)*0.22))))))

I am ashamed.

Could you take me through how your formula works?

As far as I can tell the it is (If a1 is 14000 for example):

=SUM(IF(A1>C2:C8,E2:E8,IF(A1-C1:C7>0,(A1-C1:C7)*D28,0)))

If 14000 is more than one billion fifty four thousand, then display
3000....
If 14000 minus one billion fifty four thousand is more than zero, then
display (14000 minus one billion fifty four thousand) times 0.89. Or
zero.

Wow I'm confused.

Thanks,

Jim

 

[Bernie Deitrick]

Jim,

The formula is an array formula, which means that
A1>C2:C8
is actually evaluated as if it were written as an array - arrays are in
curly braces in Excel
{A1>C2;A1>C3;A1>C4;A1>C5;A1>C6;A1>C7;A1>C8}
which, for your example, evaluates to
{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

So then the next array, E2:E8, has values

{0;100;300;750;850;1000;0}

So, for every TRUE in the first array, the corresponding value in the second
is summed: since the first four are TRUE,
0, 100,300, and 750 are passed to the SUM function.

For the first array where the value is FALSE, the second part of the IF is
evaluated. Here's the whole formula, with the arrays expanded.

=SUM(IF({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},{0;100;300;750;850;1000;0},I
F({#VALUE!;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE},{#VALUE!;14000;12000;9000;4000;-
1000;-6000}*{0;0.05;0.1;0.15;0.17;0.2;0.22},0)))

Only the fifth element is TRUE in the third array - we must ignore the
first four, since those were TRUE in the first Array - and so only 4000 *
0.17 gets passed to the SUM function.

So the final sum is

0 + 100 + 300 + 750 + 4000 * 0.17, or the 1830

HTH,
Bernie
MS Excel MVP

That is absolutely perfect.

I had this:

=IF(A2<2000,A2*0.05,IF(A2<5000,100+(A2-2000)*0.1,IF(A2<10000,400+(A2-5000)*0