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

 

[daleholden]

Yes i know what it means but why you adding that onto the fact table?
[TABLE="class: cms_table, width: 614"]
<tbody>[TR]
[TD]Category Price FVF needs to be on the research tab not the fact table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

No idea what that means? The formula works out the final value fee if you try it.

 

[steve the fish]

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

 

[daleholden]

Steve can you check sheet to see if i have filled it in as you suggested correctly ?

Working correctly from what i can see

 

[steve the fish]

Thats right. You could either do as you have or put the same percentage number in the primary and secondary percentages.

 

[daleholden]

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?

 

[steve the fish]

No problem. They dont mark threads here. A thanks is fine. Glad its sorted.

 

[daleholden]

HI Steve
Please check you Inbox when you get an oppurtunity

Thank You

 

[steve the fish]

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.

 

[daleholden]

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