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)

Please Login or Register  to view this content.

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