Hi,

This is our commission structure...

£'s Percentage % £ per bracket Combined Commission
0 - 2,000 5 100 100
2,001 - 5,000 10 300 400
5,001 - 10,000 15 750 1,150
10,001 -15,000 17 850 2,000
15,001 - 20,000 20 1,000 3,000
20,001 - 25,000 22 1,100 4,100
25,001 - 35,000 25 2,500 6,600
35,000+ 30

I tried
=IF(G14<2000,G14*0.05,IF(G14<5000,100+(G14-2000)*0.1,IF(G14<10000,400+(G14-5000)*0.15,IF(G14<15000,1150+(G14-10000)*0.17
etc etc, but I can't fit it all in AND it's a bit big and cumbersome.

I have a feeling that it might be an array that makes this work?

Thanks,

If I understood the table and calculation:

Create table as below (A1 to C8 in my example):

Column A are commission steps
Column B is %
Column C is combined commission

0 5.00% 0
2000 10.00% 100
5000 15.00% 400
10000 17.00% 1150
15000 20.00% 2000
20000 22.00% 3000
25000 25.00% 4100
35000 30.00% 6600

and use the follwing formula:

=VLOOKUP(G14,$A$1:$C$8,3,1)+(G14-VLOOKUP(G14,$A$1:$C$8,1,1))*(VLOOKUP(G14,$A$1:$C$8 ,2,1))

HTH
" wrote:

Hi,

This is our commission structure...

£'s Percentage % £ per bracket Combined Commission
0 - 2,000 5 100 100
2,001 - 5,000 10 300 400
5,001 - 10,000 15 750 1,150
10,001 -15,000 17 850 2,000
15,001 - 20,000 20 1,000 3,000
20,001 - 25,000 22 1,100 4,100
25,001 - 35,000 25 2,500 6,600
35,000+ 30

I tried
=IF(G14<2000,G14*0.05,IF(G14<5000,100+(G14-2000)*0.1,IF(G14<10000,400+(G14-5000)*0.15,IF(G14<15000,1150+(G14-10000)*0.17
etc etc, but I can't fit it all in AND it's a bit big and cumbersome.

I have a feeling that it might be an array that makes this work?

Thanks,

Toppers,

You are both generous and intelligent and I am incredibly grateful. I
had to change $c$8 to $C$10, though!

Could I be really cheeky and ask how it works??

Thanks

Uberyes.

Toppers wrote:
If I understood the table and calculation:

Create table as below (A1 to C8 in my example):

Column A are commission steps
Column B is %
Column C is combined commission

0 5.00% 0
2000 10.00% 100
5000 15.00% 400
10000 17.00% 1150
15000 20.00% 2000
20000 22.00% 3000
25000 25.00% 4100
35000 30.00% 6600

and use the follwing formula:

=VLOOKUP(G14,$A$1:$C$8,3,1)+(G14-VLOOKUP(G14,$A$1:$C$8,1,1))*(VLOOKUP(G14,$A$1:$C$8 ,2,1))

HTH
" wrote:

Hi,

This is our commission structure...

£'s Percentage % £ per bracket Combined Commission
0 - 2,000 5 100 100
2,001 - 5,000 10 300 400
5,001 - 10,000 15 750 1,150
10,001 -15,000 17 850 2,000
15,001 - 20,000 20 1,000 3,000
20,001 - 25,000 22 1,100 4,100
25,001 - 35,000 25 2,500 6,600
35,000+ 30

I tried
=IF(G14<2000,G14*0.05,IF(G14<5000,100+(G14-2000)*0.1,IF(G14<10000,400+(G14-5000)*0.15,IF(G14<15000,1150+(G14-10000)*0.17
etc etc, but I can't fit it all in AND it's a bit big and cumbersome.

I have a feeling that it might be an array that makes this work?

Thanks,

The three VLOOKUPs use the earnings (G14) to find a corresponding value in
the table (A1:C10). If VLOOKUP cannot find an exact match, it finds the first
value which is less than the lookup value.

The third parameter in the VLOOKUP i.e. 3,1 and 2 tells VLOOKUP to the value
from the third, first and second columns respectively in the table i.e C, A
and B

For earnings of $4000, it will retrieve the values from the second row of
the table i.e 100, 2000 and 10% which are then used in your formula [as 4000
is less than 5000]

=100+(4000-2000)*0.1 (10%)

VLOOKUP is a very common technique to solve this type of problem and can
typically replace multiple IF statements. It also makes it easy to add/delete
entries [if your commission structure changes] without having to change any
formulae.

NOTE: the data in the first column (A) of the table must be in ascending
sequence.

HTH

" wrote:

Toppers,

You are both generous and intelligent and I am incredibly grateful. I
had to change $c$8 to $C$10, though!

Could I be really cheeky and ask how it works??

Thanks

Uberyes.

Toppers wrote:
If I understood the table and calculation:

Create table as below (A1 to C8 in my example):

Column A are commission steps
Column B is %
Column C is combined commission

0 5.00% 0
2000 10.00% 100
5000 15.00% 400
10000 17.00% 1150
15000 20.00% 2000
20000 22.00% 3000
25000 25.00% 4100
35000 30.00% 6600

and use the follwing formula:

=VLOOKUP(G14,$A$1:$C$8,3,1)+(G14-VLOOKUP(G14,$A$1:$C$8,1,1))*(VLOOKUP(G14,$A$1:$C$8 ,2,1))

HTH
" wrote:

Hi,

This is our commission structure...

£'s Percentage % £ per bracket Combined Commission
0 - 2,000 5 100 100
2,001 - 5,000 10 300 400
5,001 - 10,000 15 750 1,150
10,001 -15,000 17 850 2,000
15,001 - 20,000 20 1,000 3,000
20,001 - 25,000 22 1,100 4,100
25,001 - 35,000 25 2,500 6,600
35,000+ 30

I tried
=IF(G14<2000,G14*0.05,IF(G14<5000,100+(G14-2000)*0.1,IF(G14<10000,400+(G14-5000)*0.15,IF(G14<15000,1150+(G14-10000)*0.17
etc etc, but I can't fit it all in AND it's a bit big and cumbersome.

I have a feeling that it might be an array that makes this work?

Thanks,