Round a negative decimal

M

Montana

I need -27.125 to round to -27.12, not -27.13 but I can't figre out how to do
it. Any help will be greatly appreciated.
 
J

John Bundy

btw double check to make sure it does what you want since i'm not totally
sure what you want!
 
M

Montana

That worked for -27.125 - made it -27.12, but it also made -27.126 into
-27.12 instead of -27.13
What I need is -27.125000 to round down to -27.12, but anything more, such
as -27.1250001 to round to -27.13

Excel typically rounds $.005 up to $.01; I need the round function set to
round up anything greater than .05 - like .05000001, & not round up the .05

I hope that I am making sense on this. Thanks in advance for any help.
 
R

Ron Rosenfeld

I need -27.125 to round to -27.12, not -27.13 but I can't figre out how to do
it. Any help will be greatly appreciated.
Click to expand...

And what do you want to happen with other positive or negative values?
--ron
 
M

Montana

In a dollar format, I need anything from 0 to 1/2 cent to round down. (Excel
defaults to rounding up at .5, or .05, or .005) Anything above the .5
threshold, I want it to round up as normal. For example,

27.125 round to 27.12 (excel rounds this to 27.13)
27.1250001 round to 27.13
-27.125 round to -27.12 (excel rounds this to -27.13)
-27.12500001 round to -27.13
 
R

Ron Rosenfeld

In a dollar format, I need anything from 0 to 1/2 cent to round down. (Excel
defaults to rounding up at .5, or .05, or .005) Anything above the .5
threshold, I want it to round up as normal. For example,

27.125 round to 27.12 (excel rounds this to 27.13)
27.1250001 round to 27.13
-27.125 round to -27.12 (excel rounds this to -27.13)
-27.12500001 round to -27.13
Click to expand...

See if this does what you want. I have not tested it extensively:

=CEILING(A1-SIGN(A1)*0.005,SIGN(A1)*0.01)


--ron
 
J

JMB

There may be a shorter way, but you could try:
=ROUND(A1,2)-0.01*(A1*100-INT(A1*100)=0.5)*SIGN(A1)
 
Top