Quote Originally Posted by albertc View Post
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
Hello everybody.

Sorry for the way I named this thread.

Anyways, I got someones well appreciated help and this is what he came up with which works very well;

You can use a user defined function.

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


Code:
---------
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..

The problem is that this fuction disapears when I close my spreadsheet.
I am saving it but still I loose the function.

What am I doing wrong?

Cheers