Excel Formula Problem

  Devious Dave 21:41 15 Apr 2005
Locked

Hi, This one is for all you excel experts. I am currently working on a spreadsheet that will automatically work out the price I should sell a product for when I enter in the price I paid for it.

The things I sell I make different percentage markups on, for example apples I make 50% and oranges 25% (on what I pay for them). There are three different profit markups 25%,40% and 50%. This makes an IF function impossible, is there a way to do it using the LOOKUP function? Would have the following columns:

Product description
category i.e 1,2,3
Cost (what I paid for it, I enter this in manually)
Price (what I sell it for)

Category 1 = Vegetables 25% markup
Category 2 = Fruit 40%
Category 3 = Other stuff 50%

IF (product category = 1 then add 25% onto the cost)
IF(product category = 2 then add 40% onto the cost)
IF(product category = 3 then add 40% onto the cost)

Would I have t use visual basic? If so could someone explain it in simple terms as visual basic makes my brain turn to mush.

Hope someone will understand what I am trying to do as its quite hard to explain.

  stalion 21:45 15 Apr 2005

VoG™ will be here to help you shortly good evening VoG™

  Number 7 23:13 15 Apr 2005

If the product category (1,2 or 3) is in column A and the price you paid for the product is in column B, then the selling price can be calculated by entering the following formula in column C.

=IF(A1=1,B1*1.25,IF(A1=2,B1*1.4,IF(A1=3,B1*1.5,"")))

If the product category isn't 1, 2 or 3, or is blank, the selling price will also be blank.

  daba 23:34 15 Apr 2005

Or you could build a 'table' of products, and their markup percentages.

say your products were listed in the first column of the table, and the markup in the second, your formula for the markup would be

=B1*(1+VLOOKUP(A1,MarkupTable,2,FALSE)/100)

In the above A1 = "apples", B1=cost price

"MarkupTable" is a named range of cells, in the example case a table array in F2:G4 which contains a list of products in first column (F2,F3,F4) and their respective markup percentages in second column (G2,G3,G4).

The MarkupTable need not be on the same worksheet.

adavntages of this method, your product table can be extended at any time without having to edit all your formulas, and you can vary the product markup percentages by changing just one cell in the markup table array.

  Devious Dave 23:35 15 Apr 2005

Number 7 Thats sounds good, will give it ago tomorrow and report back. Can't be arsed to try tonight, want to watch the end of james bond. Thanks, David.

  Devious Dave 23:38 15 Apr 2005

Thanks daba will try that too.

"adavntages of this method, your product table can be extended at any time without having to edit all your formulas, and you can vary the product markup percentages by changing just one cell in the markup table array"

That a good point. Will play around with it tomorrow.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?