Excel 2007 : eBay comission code, various percentages...

Hello. This is my first time here so please be gentle.
I have this spreadsheet that I use to calculate my sales price for my items on eBay.
In order to calculate eBays comissions, I have to imput manually the costs into the fields.
I want to do this atomaticly. It is a rather confusing methode eBay uses.
I have 0 to 29.99 comission fee is 5.25%, 30 to 99.99 comission fee is 3%, 100 to 199.99 comission fee is 2.5%, from 200 to 299.99 comission fee is 2% and from 300 to 599.99 comission fee is 1.5%.
As we can see this is a tad complicated.
What I am doing is uppon finding my selling price for an item, I then add these numbers manually.
As an example;
Item price is 215.31, I then do this;
29.99
69.99
99.99
15.34
the percentages are found automatacly and then added all together.
What I need is to creat a formula that would take the selling price and then cut it down into those value ranges if I am making any sence.
Can anyone help me with this one?
Any help well appreciated.
Cheers,
Albert

You can use a user defined function.

Paste the following into a new module sheet in the VBA editor (Alt F11). (Insert - Module)

Function EBAyCommission(Cell As Range)
PriceRemaining = Cell.Value
Do While PriceRemaining > 0
    Select Case PriceRemaining
        Case PriceRemaining > 300
            EBAyCommission = 0.015 * (PriceRemaining - 300)
            PriceRemaining = 300
        Case Is > 200
            EBAyCommission = EBAyCommission + (0.02 * (PriceRemaining - 200))
            PriceRemaining = 200
        Case Is > 100
            EBAyCommission = EBAyCommission + (0.025 * (PriceRemaining - 100))
            PriceRemaining = 100
        Case Is > 30
            EBAyCommission = EBAyCommission + (0.03 * (PriceRemaining - 30))
            PriceRemaining = 30
        Case Else
            EBAyCommission = EBAyCommission + (0.0525 * (PriceRemaining))
            PriceRemaining = 0
    
    End Select
Loop
End Function

You should then have a new function called EBAyCommission available on your spreadsheet..

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Note: Will all senior members please support the Moderators by not answering questions that obviously break the Forum Rules