Commission Spreadsheet

[AshlyLeigh]

Hello Everyone,

I'm trying to work out the commission made on a total

So for example:

Cell U54 is the total commission.

Then in Cell T57i would like it to work out the first £5000 at 12.5%
Then in cell T58 i would like it to work out 5000-9000 at 15%
Then in cell T59 i would like it to work out 9000-13000 at 20%
Then in cell T60 i would like it to work out 13000- 20000 at 25%
Then in cell T61 i would like it to work out 20000+ at 40%

But say if i have commission of 6000 then i would have the first 5000@12.5% and then a 1000@15%

If anyone can help i would be very grateful, Thank you

 

[Snakehips]

AshlyLeigh,

Welcome to MrExcel.

When you say U54 = total commission, I'm assuming you actually mean the ammount on which commission is being paid?

If that is so then here is a start for you....

Excel 2007

	T	U
55		13000
56
57	625
58	600

<TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula T57 =IF(U55>0,MIN(5000,U55)*0.125,"") T58 =IF(U55>5000,MIN(U55-5000,4000)*0.15,"") <TBODY> </TBODY>

<TBODY>
</TBODY>

You should be able to extend similar logic to the other bands.
Hope that helps.