OnePlus 5 review
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:
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.
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 the product category isn't 1, 2 or 3, or is blank, the selling price will also be blank.
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
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.
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.
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.