How can I determine multi-level value fees from a table of fees for a given value?

[daleholden]

HI Guys/Girls

I need to change my formulas and/or lookup table structure to calculate a new fee structure bring implemented in July by eBay.
I need to determine multi-level value fees from a table of fees for a given value.

Reader background info
If you look at the Research Tab
This is used to work out eBay FVF for the specific category. From A2 you select the correct Category.
In B2 you enter supplier costs
C2 will auto adds 40% (which is of no concern for this query).
D2 works out price with my current formula =MIN(VLOOKUP($B$2,AllFees!$G$2:$H$64,2,0)I2,VLOOKUP($B$2,AllFees!$G$2:$I$64,3,0))1

Most of it is basic but, in some areas, a subset of a category has a max charge. So, if you look under Home & Furniture which is currently 10% of the final value fee.

But if it is an Appliance which is a subcategory of Home & Furniture the price cannot go over £20 whatever the price of the item.

Requirements

I need to edit the D2 formula so it can work out the new charges and Categories.
The current way just has a max fee on some items. But from July some of the items have different % split across the price and I need to know how to get it working.

New Rules I have added a new tab called NewAllFees there are only a few changes but not sure how the formula or if the current lookup table will work.
Not sure if the Fees table needs an extra column and a new formula that will work correctly.

In the NewAllFees area you can see what eBay is changing and examples. In my case shipping is free so this should help not over complicate things.

I have noted my ranges have names even though I have not implemented them in my formulas
This is an oversight which I can be fixed in sorting out this query.

Example sheet here

Cheers

 

[steve the fish]

I presume your two vlookups work out the percentage fee and the max fee? If so then your min will work just fine. Say the max fee is £20 and the percentage works out at £8. It will produce £8. If your item is expensive and the percentage works out at £50 then your formula produces the £20, the max fee.

 

[daleholden]

HI Steve

Sorry i do not think i have explained it enough the current formula works but it is changing in July.
If you look at the newallfees you should see a image showing the new calculations on a few categories which have a split % based on price.

Eg If item is priced over 250 it price from 251 to sale price is 6% the first 250 is charged at 8% so it is a split % fee based on price.

 

[steve the fish]

Is there still a cap too? Or just the different percentages?

 

[daleholden]

• Home Furniture DIY = 10% first 250 then 6%
• Home Furniture DIY>Bath = 9% first 250 then 6% but capped at £40
• Home Furniture DIY>Furniture = 9% first 250 then 6% but capped at £40
• Home Furniture DIY>Elec Socket = 8% first 250 then 6%
• Home Furniture DIY>Power Strips = 8% first 250 then 6%

The later two are new Cats

 

[steve the fish]

Ok so you need to construct your own table but lets say it looks like this:

[TABLE="width: 614"]
<colgroup><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]Percent Change Point[/TD]
[TD]Initial %[/TD]
[TD]Secondary %[/TD]
[TD]Max Charge[/TD]
[TD][/TD]
[TD]Category[/TD]
[TD]Price[/TD]
[TD]FVF[/TD]
[/TR]
[TR]
[TD]Whatever[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD]Whatever[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]


Copy and paste that into excel. The formula for FVF is:

=MIN(IF(H2>0,MIN(H2,VLOOKUP(G2,A:E,2,0))*VLOOKUP(G2,A:E,3,0),0)+MAX(0,H2-VLOOKUP(G2,A:E,2,0))*VLOOKUP(G2,A:E,4,0),VLOOKUP(G2,A:E,5,0))

See if that works for you.

 

[daleholden]

HI Steve

I have done a new table which i have named but the formula does not refer to it?

 

[steve the fish]

You need to adapt the formula to your table. This gives you means to do that based on the table i created. Your table will need all the elements ive used if your description of what the charges are is accurate. Essentially the formula is this:

=MIN(IF(Price>0,MIN(Price,PerCentChangeValue)*InitialPercentage,0)+MAX(0,Price-PerCentChangeValue)*SecondaryPercentage,MaxCharge)

 

[daleholden]

Hi Steve
The table you show will not work the last 3 column headers stop this being a fact lookup table
I have 1000's of item under these headings all at different prices.
Why am i adding Category twice the price ? and FVF?

I do not understand how this can be a lookup fact table if you want to list every price?

 

[steve the fish]

Price being what the customer paid for the item inc postage.