A few weeks ago, I received a question from a reader that was trying to create a list of prices for merchandise that he is selling. Basically, he had a list of items, with their cost, as you can see here:

He wanted to apply the following formula to calculate the prices:

The selling prices will be;

$0 – $10 – cost price only
$10.01 – $20 – cost+(cost*50%)
$20.01-$100 – cost+(cost*40%)
$100.01 – $99999 – cost+(cost*30%)

I also asked him if he wanted prices to be rounded in some way (either up or down, to the nearest dollar, etc). For now, he only needed prices to be rounded to the nearest penny.

This is another example of how a simple problem can become complex if you try to solve it all at once. I can tell you right now that there are dozens of different ways to do this. Today, I will be resolving it using “Nested If” conditions. The correct formula will likely end ip being fair long so I prefer starting by covering one alternative at a time. So in D2, I first try to get the correct price if the cost is under $10:

=IF(C2<=10,C2,1) Obviously, the "1" is simply for my formula to work. That is the part I will edit as I add conditions. Then, I will add one more condition for items that have a cost of $20 or less: =IF(C2<=10,C2,if(C2<=20,C2*1.5,1)) After a few more such steps, I eventually reached: =IF(C2<=10,C2,IF(C2<=20,C2*1.5,IF(C2<=100,C2*1.4,C2*1.3)) From that moment on, I simply needed to round to 2 decimals so: =ROUND(IF(C2<=10,C2,IF(C2<=20,C2*1.5,IF(C2<=100,C2*1.4,C2*1.3))),2) You can see the result here: