SOLVED

New Contributor

# 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

3 Replies
best response confirmed by MichelleAten (New Contributor)
Solution

# Re: 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.

# Re: Need help with writing the correct formula

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

# Re: Need help with writing the correct formula

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.