i need an excell formula that will round up to the nearest 9.99

[Guest]

In excel i want to take a cost divide it by a certian margin then round that
result up to the nearest $9.99...
For example $125 divided by a 45% margin is $227.27
i want it to round up to $229.99

 

[Biff]

Hi!

Here's one way:

=CEILING(125/0.45,10)-0.01

Or:

=CEILING(A1/B1,10)-0.01

For example $125 divided by a 45% margin is $227.27

A1 = 125
B1 = .45
C1 = A1/B1 returns 277.7777778

Biff

 

[JE McGimpsey]

One way:

=CEILING(A1/(1-0.45),10) - 0.01

 

[Harlan Grove]

JE McGimpsey wrote...

One way:

=CEILING(A1/(1-0.45),10) - 0.01

Maybe the OP does mean round up, but if the merchandise cost were, say,
11.05, the unrounded grossed up price would be 20.09. Rounding this up
to 29.99 adds another 9.90 to the price above & beyond the 45%
gross-up. Unlikely OP would sell many of such items at 29.99.

On the other hand, bidirectional rounding like

=ROUND(A1/(1-0.45),-1)-0.01

would give 19.99, which is just 0.10 below the unrounded grossed-up
price.

 

[JE McGimpsey]

Maybe the OP does mean round up, but if the merchandise cost were, say,
11.05, the unrounded grossed up price would be 20.09. Rounding this up
to 29.99 adds another 9.90 to the price above & beyond the 45%
gross-up. Unlikely OP would sell many of such items at 29.99.

On the other hand, bidirectional rounding like

=ROUND(A1/(1-0.45),-1)-0.01

would give 19.99, which is just 0.10 below the unrounded grossed-up
price.

Valid point. OTOH, your formula would mark a 13.74 item up just 31%,
cutting almost a third off the markup. It's a problem inherent in having
markup % be on the same scale as the pricing interval.

Hard to tell which is more important. It would be more rational to use
smaller intervals at lower prices.

Of course, pricing isn't always rational...