Rounding Formula

[Guest]

I know this is a simple one for you guys, but i just cant see it..
My dollar amount equals $1.17 and i want to round up to 1.20.
I have tried Ceiling & round & roundup with no luck..

=D28/L3+L35 (this is my formula so far)

Thanks

 

[Guest]

You gave a limited example, but if you want to get to the nearest 10 cents:

? application.Round(1.17,1)
1.2

so
=round(D28/L3+L35,1)

and format the cell for two decimal places.

 

[PCLIVE]

Maybe this:

=CEILING(D28/L3+L35,0.1)

HTH,
Paul

 

[Guest]

Try this:
=ROUND(D28/L3+L35,2)

If that doesn't work, try expanding the number of decimals that you are
looking at so you have a better idea of what the number really is.

-Cory

 

[Guest]

Let me save the OP some time. In my experience, rounding two decimal places
to two decimal places doesn't do anything - a third decimal place would need
to be involved. Cory, if you tested it and it seemed to work, maybe you had
your cell formatted to display one decimal place.

 

[Guest]

Tom - good catch. My earlier post had a typo, it should have been
=ROUND(D28/L3+L35,1)

 

[Guest]

Actually i am trying to make the dollar amount go up to the next nearest
nickel.
When i tried the ceiling function it went up more that what i wanted it to.
The 1.17 should be 1.20
and another line was 1.13, should be 1.15.
Does that explain better?

Thanks again

 

[Rick Rothstein \(MVP - VB\)]

Actually i am trying to make the dollar amount go up to the next nearest

nickel.
When i tried the ceiling function it went up more that what i wanted it
to.
The 1.17 should be 1.20
and another line was 1.13, should be 1.15.

Are you saying this doesn't work for you...

=CEILING(A1,0.05)

where the amount being rounded is assumed to be in A1?

Rick

 

[Guest]

Nickel?

Actually i am trying to make the dollar amount go up to the next nearest
nickel.
When i tried the ceiling function it went up more that what i wanted it to.
The 1.17 should be 1.20
and another line was 1.13, should be 1.15.
Does that explain better?

Thanks again

 

[Rick Rothstein \(MVP - VB\)]

Nickel?

http://en.wikipedia.org/wiki/Nickel_(United_States_coin)

Rick

 

[Guest]

Thany you Rick that worked!!!!!!!

http://en.wikipedia.org/wiki/Nickel_(United_States_coin)

Rick

 

[Guest]

It would have been clearer if you said round up to the next nickel. The
nearest nickel for 1.11 is 1.10. The next nickel is 1.15. So next nearest
nickel is a bit ambiguous. My opinion of course.

 

[Rick Rothstein \(MVP - VB\)]

It would have been clearer if you said round up to the next nickel. The

nearest nickel for 1.11 is 1.10. The next nickel is 1.15.

He said that, sort of, when he posted...

"My dollar amount equals $1.17
and i want to round up to 1.20"

as 1.15 would be the nearest to 1.17, but he said "round up" and his example
did show the rounded up (not nearest) value of 1.20.

Rick