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

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
243
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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.
 
Upvote 0
  • 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
 
Upvote 0
Ok so you need to construct your own table but lets say it looks like this:

CategoryPercent Change PointInitial %Secondary %Max ChargeCategoryPriceFVF
Whatever2508%6%100Whatever4000100

<colgroup><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>


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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top