Excel Help - Advanced stuff!

Thenotsowyzewun

Hello,

This is really annoying, I know that I'm really close, but I can't quite do it!
I want to calculate the eBay UK FVF (Final Valuation Fee) based on the price an item sells for (ex postage of course).
I've found the fees, they're published here, and I'm trying to do it using an IF statement because I'm used to using them.
So, here's what I've got:

=IF($H2<0.01,0,IF($H2<29.99,$H2*0.0525,IF($H2<599.99,1.57+(($H2-29.99)*0.0325),20.1+(H2-599.99)*0.0175)))

(Looks pretty scary, doesn't it!)

$ means constant (so the column can't change but the row can (i.e. as I go down the page). I've seen this done in the opposite direction (but it was for eBay USA and seems to have quite a different fee structure.

Please help (I would've put this in the eBay section, but it's more about excel than eBay).

sweep9

I'm bored watching Mike Baldwin die...

Try =IF($H2>599.99,20.1+(($H2-600)*0.0175),IF($H2>29.99,1.57+($H2-30)*0.0325,IF($H2>0,$H2*0.0525,0)))

It seems to work...

sweep9

... OK, he's now dead...

Thenotsowyzewun

Maybe it's because I'm using OpenOffice instead of Excel... (that formula doesn't work either )

Thenotsowyzewun

[STRIKE]Just realized, it'd make more sense basing this on a table (well, maybe - it'll make it easier to understand (and update when necessary)) There's a way of doing that, OK back to the drawing board![/STRIKE]

Second thoughts, my brain hurts!

Thenotsowyzewun

Done it :D (with the help of the Formula Wizard (which is very good, btw!)):
The answer is:

=IF(H2>599.99;20.1+((H2-599.99)*0.0175);IF(H2>29.99;1.57+((H2-29.99)*0.0325);IF(H2>0;H2*0.0525;0)))

So yeah, Openoffice uses slightly different syntax to Excel.

Dack

Pretty straightforward for what you need to do - it comes down to:
FV is Final Value and FVF is final value fee

if FV < 30 then FVF = FV * 5.25 %
ELSE
if FV < 600 then FVF = (29.99 * 5.25%) + (FV-29.99) * 3.25%
ELSE FVF = (29.99 * 5.25%) + (570 * 3.25%) + (FV - 599.99) * 1.75%

or, using your style
=IF($H2<30.00,$H2*0.0525,IF($H2<600,1.57+($H2-29.99)*0.0325,20.1+($H2-599.99)*0.0175))

This ignores the 'did not sell option' as a final value of 0 would give a fee of 0 automatically

Now overtaken by you posting the answer

And correctly for OpenOffice