Set Sell Price based on multiple rules

[tbgame]

Hi All,

I am working on a spreadsheet to help calculate the Sell Price on an item based on certain conditions. The department each product belongs to sets the rules for the pricing, sometime it can be a straight forward rule etc. all items in department Shoes = Cost * 10.

However for other departments such as Pants, the sell rules are

IF COST < $20
SELL = COST * 10
ELSEIF COST < $30
SELL = COST * 5
ELSE ANYTHING WITH COST OVER $30
SELL = COST * 2.5

What is the way to achieve the Sell Colum to include the above rules based on what Department the product belongs to?

I will end up with 50+ departments that each have different pricing rules so trying to cram all the logic into one cell with a rule that fits's all condition has been quite tricky! I was hoping using either a Formula/Macro/VBA I can get it to reference a different sheet or location that has all the rules in it that can be easily adjusted.

Thanks for your time.

Glen.

[wk9128]

Cell C3 formula , drag down

Please Login or Register  to view this content.

[Czeslaw]

Formula

Please Login or Register  to view this content.