Code Question - Calculate eBay Fees Automatically

S

shadrach

I'm trying to create a spreadsheet that will help log eBay sales. I want to
set up a formula to figure the fees once an item is sold. I'm hoping to just
enter the final sale price and in another cell with the formula in it, figure
the eBay Final Value Fees. I've tried a bunch of times and cannot get the
code correct. The fees are figured in the following way:

1- Item not sold - No Fee

2. $0.01 – $25.00 - 5.25% of the closing value
$25.01 – $1,000.00 - 5.25% of the initial $25.00 ($1.31), plus 2.75% of the
remaining closing value balance ($25.01 to $1,000.00)

3. Over $1,000.01 - 5.25% of the initial $25.00 ($1.31), plus 2.75% of the
initial $25.00 - $1,000.00 ($26.81), plus 1.50% of the remaining closing
value balance ($1,000.01 - closing value)

Is it possible to do all that in just a single formula in one cell? I've
tried doing "IF" formulas, but can't seem to get it right. Even if it has to
be in a few cell, it's be fine. Any help would be great.

Thanks!
 
H

Harald Staff

If I understand you right:

=MIN(A1,25)*5.25%+MIN(975,MAX(A1-25,0))*2.75%+MAX(A1-1000,0)*1.5%

HTH. best wishes Harald
 
S

shadrach

That's it. Works good. Thanks!!!




Harald Staff said:
If I understand you right:

=MIN(A1,25)*5.25%+MIN(975,MAX(A1-25,0))*2.75%+MAX(A1-1000,0)*1.5%

HTH. best wishes Harald
 
F

freedonian

Hi
On a smiliar note I've been trying to amend your formula for Ebay UK, but I
just can't do it. Please help:

The fee structure on Ebay UK is more complex than it's American counterpart.
It is as follows:

Item not sold - None

£0.01- £29.99 - 5.25%

£30.00 - £99.99 - 5.25% of the initial £29.99 (£1.57) plus 3.00% of the
remaining closing value

£100.00 - £199.00 - 5.25% of the initial £29.99 (£1.57) plus 3.00% of the
initial £30.00 - £99.99 (£2.10) plus 2.50% of the remaining closing balance

£200.00 - £299.00 - 5.25% of the initial £29.99 (£1.57), plus 3.00% of the
initial £30.00 - £99.99 (£2.10), plus 2.50% of the initial £100.00 - £199.99
(£2.50) plus 2.00% of the remaining closing value balance

£300.00 - £599.99 - 5.25% of the initial £29.99 (£1.57), plus 3.00% of the
initial £30.00 - £99.99 (£2.10), plus 2.50% of the initial £100.00 - £199.99
(£2.50) 2.00% of the initial £200.00 - £299.99 (£2.00) plus 1.50% of the
remaining closing value balance

£600.00 and over - 5.25% of the initial £29.99 (£1.57), plus 3.00% of the
initial £30.00 - £99.99 (£2.10), plus 2.50% of the initial £100.00 - £199.99
(£2.50) plus 2.00% of the initial £200.00 - £299.99 (£2.00) plus 1.50% of the
initial £300.00 - £599.99 (£4.50) plus 1.00% of the remaining closing value
balance

I amended your formula as follows but it doesn't generate the correct figure:

=MIN(A1,29.99)*5.25%+MIN(70,MAX(A1-29.99,0))*3%+MIN(99.01,MAX(A1-99.99,0))*2.5%+MIN(99.01,MAX(A1-199.99,0))*2.5%+MIN(100,MAX(A1-299.99,0))*2%+MIN(300,MAX(A1-599.99,0))*1.5%+MAX(A1-600,0)*1%
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top