Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Need formula to round number up to always end in X.X9

40 views
Skip to first unread message

Heather

unread,
Nov 12, 2009, 10:42:02 AM11/12/09
to
I have a spreadsheet with prices. If the price is 2.55, I want the formula
to round it up to 2.59. If it is 3.01, I want the formula to round it to
3.09. The last digit always needs to be a 9, and it always needs to round UP
to the next .x9. Any ideas?

Dave Peterson

unread,
Nov 12, 2009, 11:00:52 AM11/12/09
to
You could use this to round down to the nearest dime:
=FLOOR(A1,0.1)

Then add 9 cents
=FLOOR(A1,0.1)+0.09

--

Dave Peterson

RonaldoOneNil

unread,
Nov 12, 2009, 11:30:02 AM11/12/09
to
With your number in A1

=ROUNDUP(A1,1)-0.01

Jacob Skaria

unread,
Nov 12, 2009, 11:32:10 AM11/12/09
to
Try the below

=CEILING(A1,0.1)-0.01

If this post helps click Yes
---------------
Jacob Skaria

bapeltzer

unread,
Nov 12, 2009, 11:38:10 AM11/12/09
to
=ROUNDUP(A1+0.01,1)-0.01

This adds a penny, rounds up to the next dime, then subtracts a penny. If
you don't first add the penny, you could wind up lowering the input value.
Ex: 2.60 would round to 2.60 and then you'd deduct the penny to get 2.59.

Ron Rosenfeld

unread,
Nov 12, 2009, 3:19:14 PM11/12/09
to
On Thu, 12 Nov 2009 07:42:02 -0800, Heather <Hea...@discussions.microsoft.com>
wrote:

=CEILING(A1+0.01,0.1)-0.01
--ron

Ron Rosenfeld

unread,
Nov 12, 2009, 3:19:58 PM11/12/09
to
On Thu, 12 Nov 2009 08:32:10 -0800, Jacob Skaria
<Jacob...@discussions.microsoft.com> wrote:

>Try the below
>
>=CEILING(A1,0.1)-0.01

But 3.60 --> 3.59. If I understand the OP, it should --> 3.69
--ron

0 new messages