Search code examples
excelexcel-formulaexcel-2010

Is there an excel formula that will round negative numbers up? i.e. round(-2.5) = -2

Microsoft Office Excel 2010 rounds 2.5 to 3. It also rounds -2.5 to -3.

I'm trying to use Excel validate my data against a system that rounds -2.5 to -2. Different systems seem to use different tie breaking rules (i.e. I learned the hard way that Oracle use opposite rules).

Is there an Excel formula that will round -2.5 to -2? Creativity is also acceptable (i.e. simple macro).

It should also round:

-2.7 -> -3
-2.2 -> -2

This rounds in the opposite direction that I'm looking for:

=ROUND(-2.5,0)

EDIT: I previously said that one type of rounding is more correct than another. After more research, I didn't one more popular than another. But I did find the default IEEE rounding rule, which is strangely, break ties by rounding to the nearest half number. I'm not looking for this method though :)

Solution

  • If it's always whole numbers this should work. 

    =FLOOR(A1+0.5,1)

    If you need decimals say the number of decimals is in B1 it's as simple as 

    =FLOOR(A1*10^B1+0.5,1)/10^B1

    As barry Houdini pointed out in the comments FLOOR(, 1) is the same as INT() but without the extra parameter.

    =INT(A1*10^B1+0.5)/10^B1