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
With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
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
Featured Post
With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
=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