Forum Discussion
Need help with writing the correct formula
Currently using a PC with Windows 10 and the Excel versions is 16051.14527.20276.0
I am creating a price list for materials used on service call jobs and I need to show our cost and the price including the markup.
The markup amount depends on the cost of the item. Specifically:
If the item costs $10.00 or less the cost is doubled so it is multiplied by 2
If the item costs $10.01 to $100.00 the cost is multiplied by 1.6
If the item costs over $100.00 then the cost is multiplied by 1.7
I already have pages and pages of parts and their costs. If a formula exists to calculate my prices, it would be a HUGE help to just add a column with such a formula to get the all the marked up prices at once. If not, I will have to calculate each item one at a time.
If this is not the right forum to ask this question, I apologize and would appreciate direction on where I should post. Otherwise any help with this is greatly appreciated.
Thank you,
Michelle
Let's say the prices are in D2 and down.
In another cell in row 2, enter the formula
=D2*LOOKUP(D2,{0,10.01,100.01},{2,1.6,1.7})
Fill down.
Alternatively, use a small lookup table:
Here, the formula in E2 is
=D2*VLOOKUP(D2,$H$2:$I$4,2)
The advantage of this approach is that if you change the thresholds or the multipliers, you need to do it only in the lookup table, instead of in the formulas.
3 Replies
Let's say the prices are in D2 and down.
In another cell in row 2, enter the formula
=D2*LOOKUP(D2,{0,10.01,100.01},{2,1.6,1.7})
Fill down.
Alternatively, use a small lookup table:
Here, the formula in E2 is
=D2*VLOOKUP(D2,$H$2:$I$4,2)
The advantage of this approach is that if you change the thresholds or the multipliers, you need to do it only in the lookup table, instead of in the formulas.
- Thank you very much. I am going to try both of these and see how it turns out. I will let you know.
- Once I entered the 1st formula in my spreadsheet, it worked PERFECTLY!! You saved me so much time, if I could hug you I would.
