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
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
