Forum Discussion
Need help with writing the correct formula
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.
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.
