How can I round up and format prices to .95 ?

  • Thread starter Thread starter bianling
  • Start date Start date
B

bianling

Hello:

I'm trying to convert a column of prices, so that those prices ar
rounded up to x9.95.
That is 1 dollar will be converted to $9.95. $11 will be converted t
$19.95. $26.00 will be converted to $29.95, etc...

I can't figure out the formula so far.

Thank you for replying
 
Here's one way

=ROUNDUP(A17+0.00001,0)-0.05

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob
this would round 9.96 also to 9.95 (and not 19.95). Also this would
round to a full dollar and not to full 10$. Depending of what the OP
wants he may use
=ROUNDUP(A17+0.05,-1)-0.05
 
Frank that's true. With your correction, do you thin k he really wants to
round 19.96 to 29.95, that's perverse rounding<g>.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob
wasn't sure about this either - but if this is for a sales department
who knows <ebg>
So maybe the OP comes back and states what is the correct rounding for
him
 
LOL - Hi Frank.

By the way, just in case you thought I was deserting my post lately, I'm buried
in a pretty hectic proposal at the moment, so free time is somewhat of a
luxury - Hopefully not for long though :-)
 
Hi Ken
was already wondering where you were :-)
But this explains it all. Wish you good luck for your proposal
 
Thank you for all your posts.

I guess it does seem like an awkard way of rounding up prices.

I was also requested if I had prices such as $10 or $100 to round the
down instead to $9.95 or $99.95, as opposed to $19.95 and $109.95.

As that might make more sense to most retail type stores. But tha
would probably greatly complicate a single global formula.

I would've like more explanations as to how you came up with th
formulas. But thanks for your feedback so far
 
I believe I have a solution for both the questions you have requested
You can use the Ceiling and Floor functions.
The ceiling and floor formulas take the input and rounddown (Floor) o
Roundup(Ceiling) to the nearest integer rounded to the value in th
second parameter. In the third example, 26 is rounded down to th
nearest 10 which is 20 and then we add on 9.95 to get 29.95. Note th
last example using 5, which rounds to the next nearest 5.

For the
Input Formula Result
1 =FLOOR(A1,10)+9.95 9.95
11 =FLOOR(A2,10)+9.95 19.95
26 =FLOOR(A3,10)+9.95 29.95
100 =CEILING(A4,10)-0.05 99.95
10 =CEILING(A5,10)-0.05 9.95
7 =FLOOR(A6,5)-0.05 4.9
 
Back
Top