IF statement for price markup

[bwaaack]

Someone please solve this equation. I have a markup equation that I fill down. I'm trying to net no less that 5.00 but I have products with a wide range of total cost.

Price = Total cost + Markup percentage. Net = Price - Total cost. That part works great. The following is what i'm trying to figure out.

If net equals < 5.00 at the original markup, than Markup X% with a max net of 5.00. Only for the net amounts that were under 5.00 at the original markup.

Thank you so much smart people

 

[brownbread]

See if I'm understanding correctly...

Columns A and B have numbers typed in. Column B is my standard markup percentage, which is 22% (0.22).

Columnc C / D / E are formulas - here are the row 2 formulas, which are dragged down to other rows:
C2: =A2*(1+B2)
D2: =IF(A2*(1+B2)-A2<5,A2+5,A2*(1+B2))
E2: =(D2-A2)/A2

Column C is our initial price calculation, which we're NOT going to use... it adds our standard markup percentage to the total cost.

Column D is our new price calculation. It uses the standard markup percentage to check if the resulting price difference is smaller than 5: If so, it just adds 5 to the total cost; otherwise it goes with the standard markup percentage.

Column E confirms what exact markup has been applied. With row 2, it had to resort to the minimum markup of 5; whereas on rows 3 and 4 the standard markup percentage was enough to get over the minimum markup amount.