Nested IF's for eBay fees

[boznian]

I am trying to write a formula to calculate selling fees. We are now able to sell using fixed price auctions on eBay, and eBay has just changed their fee structure - plus we also sell on an eCommerce site. So we have 3 possible fee structures. I have the following formula to calculate the new eBay fees, and aside from returning "FALSE" if W3 is blank, it works fine for an item listed as an auction:

=IF(W3="Auction",IF(L3<=9.99, 0.25, IF(L3<=24.99, 0.5, IF(L3<=49.99, 0.75, IF(L3<=199.99, 1, IF(L3>=200, 2,""))))))

What I need is for the formula to look at cell W3 and do the following:

If W3=blank, return a blank cell
If W3="Auction", do the fomula above
If W3="Fixed", return "0.20"
If W3="BC", return "0.00"

BTW, cell W3 has data validation to ensure only 1 of the 3 values listed. I also need to update my final value fee formula to do pretty much the same thing, but I think if I can get this one resolved I'll be able to apply it to the other.

Thanks!

 

[dscg]

Try:

=CHOOSE(MATCH(W3,{"Auction","Fixed","BC",0},0),IF(W3="Auction",IF(L3<=9.99, 0.25, IF(L3<=24.99, 0.5, IF(L3<=49.99, 0.75, IF(L3<=199.99, 1, IF(L3>=200, 2,"")))))),"0.20","0.00","")

 

[boznian]

That works perfect! Thanks! Now I have to try to deconstruct it to figure out what you are doing, and then apply it to my final value fees formula...

 

[boznian]

OK I am just not getting anywhere with the second part of my problem - I thought I could figure it out myself based on the previous reply but no go. I need another formula to calculate the final value fees.

I have this working formula for Auction final value fees but it is out of date as of April 1:
=IF(AD31,IF(AD31>25,IF(AD31>1000,(31.13+(AD31-1025)*0.015),(2.19+(AD31-25)*0.035)),0.0875*AD31),"")

I honestly can't wrap my head around how it's working well enough to even change it to the new fee structure:
0.01 - 50.00 = 8% of sale price
50.01 - 1000.00 = 8.75% of initial $50, plus 4.0% of remaining final sale price up to $1,000
1000.01 or more = 8.75% of initial $50, plus 4.0% of remaining final sale price up to $1,000, plus 2.0% of remaining final sale price

Fixed Structure - I don't have an existing formula for this one, but should be similar to above?:
0.99 - 50.00 = 12% of sale price
50.01 - 1000.00 = 12.0% of the initial $50, plus 6.0% of the remaining final sale price up to $1,000
1000.01 or more = 12.0% of the initial $50, plus 6.0% of the remaining final sale price up to $1,000, plus 2.0% of the remaining final sale price

So:

If W3=blank, return a blank cell
If W3=Auction do the final value fee formula for auctions
If W3=Fixed do the final value fee formula for Fixed price listings
If W3=BC return 0.00

If someone could help me along with this I would really appreciate it. I have been out of Excel for so long my brain has rusted.

 

[boznian]

I am beginning to wonder if this formula ever worked properly.
=IF(AD31,IF(AD31>25,IF(AD31>1000,(31.13+(AD31-1025)*0.015),(2.19+(AD31-25)*0.035)),0.0875*AD31),"")

I cannot adjust it to reflect the new fees let alone write another for Fixed price auctions. Just can't get it and can't find what I need after hours of googling. Stuck big time...

 

[boznian]

I have been at this all day and night and still stuck. The following formula is close, my attempt to adapt the 'match' formula kindly given to me earlier:

=CHOOSE(MATCH(W3,{"a","f","e",0},0),IF(W3="a",IF(X3<=50,X3*0.0875,IF(X3<=1000,4.38+((X3-50)*0.04),IF(X3>1000,38+((X3-1000)*0.02),))),IF(X3<=50,X3*0.12,IF(X3<=1000,6+((X3-50)*0.06),IF(X3>1000,63+((X3-1000)*0.02),))),"0.00","")

This (if it was working) would calculate the final value fees based on whether it was an auction, fixed price listing or eCommerce site sale (no fee). So close, but it doesn't like the "0.00", which is what I want displayed if W3="e".

Can anybody fix this for me?? And/or tell me what I am doing wrong??

 

[dscg]

Try this modification, your last formula was very close, you just had an extra IF(W3="a" that isn't needed. Also note 42.375 instead of 38

=CHOOSE(MATCH(W3,{"a","f","e",0},0),IF(X3<=50,X3*0.08,IF(X3<=1000,4.375+((X3-50)*0.04),IF(X3>1000,42.375+((X3-1000)*0.02)))),IF(X3<=50,X3*0.12,IF(X3<=1000,6+((X3-50)*0.06),IF(X3>1000,63+((X3-1000)*0.02)))),0,"")

 

[boznian]

Resolved!

Just wanted to say thanks to dscg!!!