Need help with writing the correct formula

[MichelleAten]

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

[#2 · Accepted solution]

@MichelleAten 

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]

Thank you very much. I am going to try both of these and see how it turns out. I will let you know.

[#4]

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.