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
 
Yes i know what it means but why you adding that onto the fact table?
Category Price FVF needs to be on the research tab not the fact table

<tbody>
</tbody>
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
No idea what that means? The formula works out the final value fee if you try it.
 
Upvote 0
Ok so i looked at your table. Its incomplete. You need to fill it in properly. If there is no secondary percentage then fill in a stupid large number for the switch point in column B. Then the secondary percentage will never be used. Then your formula:

=MIN(IF(C2>0,MIN(C2,VLOOKUP(A2,eBayFees!A:E,2,0))*VLOOKUP(A2,eBayFees!A:E,3,0),0)+MAX(0,C2-VLOOKUP(A2,eBayFees!A:E,2,0))*VLOOKUP(A2,eBayFees!A:E,4,0),VLOOKUP(A2,eBayFees!A:E,5,0))
 
Upvote 0
Steve can you check sheet to see if i have filled it in as you suggested correctly ?

Working correctly from what i can see
 
Last edited:
Upvote 0
Thats right. You could either do as you have or put the same percentage number in the primary and secondary percentages.
 
Upvote 0
HI Steve
I have done as you suggested, plus can i thank you for your help
Is there a way a marking it as solved?
 
Last edited:
Upvote 0
No problem. They dont mark threads here. A thanks is fine. Glad its sorted.
 
Upvote 0
You want to add 20% to the result? Just times the whole lot by 120%. Put brackets around the current formula and add *120% to the end.
 
Upvote 0
So do you mean like this

=MIN(IF(C2>0,MIN(C2,VLOOKUP(A2,eBayFees!A:E,2,0))*VLOOKUP(A2,eBayFees!A:E,3,0),0)+MAX(0,C2-VLOOKUP(A2,eBayFees!A:E,2,0))*VLOOKUP(A2,eBayFees!A:E,4,0),VLOOKUP(A2,eBayFees!A:E,5,0)*120%)

I am getting a N/A !!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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