Formula Query

[biggest_apple]

I'm hoping someone out there may be able to help with a formula that
would be of tremendous help to me.
As someone who does a good deal of trading on ebay I use excel 97 to
keep a running tab on my profit margins. The problem is I really want
it to be more accurate. As ebay charges a final 'Value Fee' based on a
percentage of the final sale price, I wanted to know how I could use
this in a formula. The fees range as follows:

Selling price of £0 - £29.99 - Ebay takes - 5.25%
Selling price of £30.00 - £599.99 - Ebay takes - 3.25%
Selling price of £600.00 and up - Ebay takes - 1.75%

So is there a way to have a formula that says if the amount in cell A1
is between £0-£29.99 then deduct 5.25% of the actual number between
these amounts? If so can you give me an example of this formula so that
it will adjust for all of the price ranges and their respective fees?

Many thanks!

 

[Peo Sjoblom]

One way

=IF(A1="","",A1*(1-VLOOKUP(A1,{0,0.0525;30,0.0325;600,0.0175},2)))

where A1 holds the finals sale price, then the formula will return what's
left after Ebay
has taken its commission

 

[Peo Sjoblom]

You probably want to round that to the nearest pence

=IF(A1="","",ROUND(A1*(1-VLOOKUP(A1,{0,0.0525;30,0.0325;600,0.0175},2)),2))

--

Regards,

Peo Sjoblom

One way

=IF(A1="","",A1*(1-VLOOKUP(A1,{0,0.0525;30,0.0325;600,0.0175},2)))

where A1 holds the finals sale price, then the formula will return what's
left after Ebay
has taken its commission

 

[Harald Staff]

Try

=A1*(1-(0.0175+(A1<600)*0.015+(A1<30)*0.02))

 

[biggest_apple]

*You probably want to round that to the nearest pence

=IF(A1="","",ROUND(A1*(1-VLOOKUP(A1,{0,0.0525;30,0.0325;600,0.0175},2)),2))

--

Regards,

Peo Sjoblom*

Thanks for that. Just to make it that much more complicated:
As I have my sheet currently set up all final sale prices are set up in
column *F* . The amount I originally paid for the item sold is in
column *G* . I'd like the final amount of profit to be in column *I*.
So what would I need to enter so that I'm subtracting the original
purchase price (column G) from column F as well as subtracting ebay's
fees and having the net amount displayed in column I? Can this be done
so that the formula effecta each row of the columns or does the formula
need to manually entered into each row?

 

[Harald Staff]

How to subtract i Excel ?
=F1-G1

Manual entries. Or perhaps get to know your friend "The Fill Handle" for copying.
http://www.mnstate.edu/ronwilliams/Classes/Tech_394/Excel/Fill Handle.htm

 

[biggest_apple]

Subtracting in Excel is probably the one thing I can do but thanks

 

[Harald Staff]

I guessed so, but you never know

There's a setting in some Excxel versions' Tools > Options > Edit menu saying "Extend list
formats and formulas" that tries to make this row similar to previous row.

If this doesn't apply either then you have to rephrase the question. Ok, no you don't have
to. I just didn't understand it.

 

[biggest_apple]

Anyone else got an idea of how to do this?

Thanks

 

[J.E. McGimpsey]

Here's how I'd do it:

At least in the US, eBay's commission is *tiered*, meaning that if
the selling price is $100, eBay takes 5.25% of the first $25.00,
then 2.75% of the next $75, not 2.75% of $100. If that's how it
works for the OP, this formula will give you a better calculation
for eBay's fees:

=ROUND(SUMPRODUCT(--(F2>{0,30,600}), --(F2-{0,30,600}),
{0.0525,-0.02,-0.015}), 2)

So to calculate your net profit in one go:

I2: =IF(F2, ROUND(F2 - G2 - SUMPRODUCT(--(F2>{0,30,600}),
--(F2-{0,30,600}), {0.0525,-0.02,-0.015}), 2), "")

and copy down as far in column I as desired.

For an explanation of how the guts of the formula works, see

http://www.mcgimpsey.com/excel/taxvariablerate.html

If the fees aren't tiered, plug Peo's formula into the above.

 

[J.E. McGimpsey]

Actually,

=IF(F2, ROUND(SUMPRODUCT(--(F2>{0,30,600}), --(F2-{0,30,600}),
{0.9475,0.02,0.015})-G2, 2), "")

is a bit more efficient.

 

[biggest_apple]

* *

[/QUOTE]

This formula seems to come the closest though doesn't seem to be 100%.
The other formula doesn't work at all - presents negative numbers which
isn't possible.

The next question is how do I automate the formula to recognize the
next row number? I'm up to 539 rows so far so the idea of manually
changing the formula to reflect each of the 539 rows deosn't sound that
appealing if you know what I mean