Calculating Accumulative Commission

**poshgaffer** · 06-28-2007, 01:58 PM

Hello,

Could someone help me set up the following please?

Sales figure = x

Sales from Band 1 £0 to £3000 = zero % commission
Sales from Band 2 £3001 to £10000 = 5% commission
Sales from Band 3 £10001 to £25000 = 10% commission
Sales from Band 4 £25001 & above = 25% commission

Many thanks

Poshgaffer

**oldchippy** · 06-28-2007, 04:44 PM

Originally Posted by poshgaffer

Hello,

Could someone help me set up the following please?

Sales figure = x

Sales from Band 1 £0 to £3000 = zero % commission
Sales from Band 2 £3001 to £10000 = 5% commission
Sales from Band 3 £10001 to £25000 = 10% commission
Sales from Band 4 £25001 & above = 25% commission

Many thanks

Poshgaffer

Hi Poshgaffer,

May be this will help?

**poshgaffer** · 06-28-2007, 04:57 PM

Perfect...

Many thanks

Poshgaffer

**oldchippy** · 06-28-2007, 04:58 PM

Glad to help - thanks for the feedback

**poshgaffer** · 06-29-2007, 04:21 AM

Dear Chippy,

I think that there is an error on the 25% bracket. If my sales are say, £27,892 I should get a total commission of £2347.25.

Using the table it reads £6,973

Could you have a look at this please? The 25% is only paid on sales above £25,000.

Kind regards,

poshgaffer

**oldchippy** · 06-29-2007, 04:37 AM

How do you arrive a £2,347.25? Only the table calculates correctly - 25% of £27,892 is £6,973

**poshgaffer** · 06-29-2007, 05:02 AM

What I mean is that 25% commission is paid on the part that goes over £25K ie: 25% of £2,789.20 in this case.

**oldchippy** · 06-29-2007, 06:38 AM

Doesn't that equal £2,892?

**poshgaffer** · 06-29-2007, 07:00 AM

It equals £697.30

Regards,

poshgaffer

**oldchippy** · 06-29-2007, 04:51 PM

Hi poshgaffer,

You must have a different calculator to me?

£27,892 - £25,000 = £2,892 * 25% = £723

Do you agree?

**daddylonglegs** · 06-29-2007, 07:05 PM

Given your bands from your first post, does this stack up?

If total is £27892 then you want to pay

0% on the first £3000 = £0
5%on the next £7000 = £350
10% on the next £15000 = £1500
25% on the remainder, i.e. £2892 =£723

total = £0+£350+£1500+£723 = £2573

There are several ways to get that result in Excel, here are 2 that use a single formula, assuming your amount is in A1

=A1*25%-MIN(A1,25000)*15%-MIN(A1,10000)*5%-MIN(A1,3000)*5%

or

=SUMPRODUCT(--(A1>{0,3000,10000,25000}),A1-{0,3000,10000,25000},{0,0.05,0.05,0.15})

the second of these can be adapted to read from a table, so if your commission percentages or bands change you just change the table rather than the formula. See here for a description of this method courtesy John McGimpsey

edit: you may not recognise the percentages used, i.e. 25%, 15%, 5%, 5%. These are correct - they represent the difference between each band, i.e. 25%-10% = 15%, 10%-5% = 5% etc.