Commission schedule formula

Hello,

I am trying to work out the formula I need to calculate commission of the following.

£0-£3500 Sales = 0% Commission
£3500-£20000 Sales = 20% Commission
£20000-£40000 Sales = 30% Commission
£40000+ Sales = 40% = 40% Commission

i.e. Sales of £11500 would be commission of £2300 because the first £3500 = £0 and from £3500-£11500 @ 20% = £2300 Commision.

Example 2: Sales of £60000 would = comission of £17300 because the first £3500 = £0 and from
£3500-£20000 = £3300 commission at 20% +
£20000-£40000 = £6000 commission at 30% +
£40000-£60000 = £8000 @ 40% comission.

I hope this makes sense and thank you in advance for your help.

Kind regards

Richard

I created a table with the minimum sales in column A, the percentage for commission in column B and the amount earned from lower tiers of commission in column C. If your sales are in Cell A8, the formula is as follows (example attached)

=(A8-VLOOKUP(A8,Table,1))*VLOOKUP(A8,Table,2)+VLOOKUP(A8,Table,3)

Does this work for you?

ChemistB

Have a look at this file.

.

Also you can see here three methdos.

.

Cheers guy's, they all do what I need many thanks. Rich