Home |
Search |
Today's Posts |
#1
|
|||
|
|||
i need an excell formula that will round up to the nearest 9.99
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 |
#2
|
|||
|
|||
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 "Brian" <Brian @discussions.microsoft.com wrote in message ... 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 |
#3
|
|||
|
|||
One way:
=CEILING(A1/(1-0.45),10) - 0.01 In article , "Brian" <Brian @discussions.microsoft.com wrote: 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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
In article .com,
"Harlan Grove" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
Frequency formula | Excel Worksheet Functions | |||
excell formula 9+5=4 not 14 | Excel Worksheet Functions | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions |