I have an Excel database with a single column showing different product cost and I want to add markup to each product cost based on that product cost.
For instance if the cost of the product is less than $10 then markup is 25%, but if cost is >$10 but < than $20 markup is 22%, if cost is >$20 but <$30 then markup is 20%, etc. And the result of the calculation put in a seperate column.
Was considering using the =LOOKUP but not sure of the formula:
Here's what I came up with but Excel doesn't like it. (A1 is one cell with the actual Cost).
first 'if' deals with value > 30 and if its not the else portion evaluates next
Second 'if' deals with value < 10 and 'else' has to be in the 22% range
Excel wasn't liking your LOOKUP formula because you put cell references inside an array constant. If you pull those out, the formula should work:
=LOOKUP(A1,{10,20,30},A1*{0.25,0.22,0.2}+A1) how your formula could be converted
=LOOKUP(A1,{10,20,30},A1*{1.25,1.22,1.2}) alternative conversion
If you are going to take this approach, make sure that you include a 0 bracket. If you don't, the formula returns an error if A1 is less than the smallest value in the first array constant. The first formula below adds a 0 bracket, while the second one converts the 10 bracket into a 0 bracket.
=LOOKUP(A1,{0,10,20,30},A1*{0.30,0.25,0.22,0.2}+A1)
=LOOKUP(A1,{0,20,30},A1*{0.25,0.22,0.2}+A1)
0
nucomputerguyAuthor Commented:
Thanks for the help. Your formulas turned on a light and made it happen! Love the array concept. Thanks
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
=IF(A1>30,A1*1.2,IF(A1<10,
first 'if' deals with value > 30 and if its not the else portion evaluates next
Second 'if' deals with value < 10 and 'else' has to be in the 22% range
Edit the 30 and 10 as required