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 Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points youâ€™re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.
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
Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention. Check out this how-to article for more information.
=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