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
56
57
58

<TBODY>
[TD="align: right"][/TD]
[TD="align: right"]13000
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #ffff00, align: right"]625
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]600
[/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]T57
[/TH]
[TD="align: left"]=IF(U55>0,MIN(5000,U55)*0.125,"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]T58
[/TH]
[TD="align: left"]=IF(U55>5000,MIN(U55-5000,4000)*0.15,"")
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]


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