Building a commission spreadsheet

[Taylor0692]

Hi All,

Relatively beginner level with Excel but I've been tasked with creating a spreadsheet to track and calculate sales commission at my work.

It's a tiered commission with the below structure based on monthly billings:

£0-£4000 = 0%
£4,001-£15,000 = 20%
£15,001+ = 30%

Does anyone have any tips on how's best to go about creating the formula's to calculate this - I've checked a few different places and they seems to suggest an IF or VLOOKUP but not sure what's best.

Help is much appreciated!

Thanks

[danielexcelvba]

If your sales are set up in column "A" and you want your commissions to appear in column "B", you could paste this into cell "B2" and copy down (see attached).

Please Login or Register  to view this content.

[Taylor0692]

Thanks for your help but it's not quite what I need.

The structure is that you receive no commission on the first £4000 you earn, 20% between £4000-£15000 and over £15000 is 30%.

For example if someone billed £20,000, they'd earn:

£0-£4000 = £0
£4000-£15000 (20%) = £2200
£15000-£20000 (30%) = £1500

This is where I was having the difficulty in creating the right nested IF's!

[danielexcelvba]

Ah I misunderstood sorry that's a little trickier but should be doable

[danielexcelvba]

You could try this

Please Login or Register  to view this content.

[Taylor0692]

Perfect - thanks for your help!

[exceldoeasy]

=if(and(a2>0,a2<=4000),(a2*0/100),if(and(a2>4000,a2<=15000),(a2*20/100),if(a2>15000,(a2*30/100),)))

[danielexcelvba]

You're welcome