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

Question

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

I am trying to use Excel to check my data against a system that is between -2.5 and -2. It seems that different systems use different disconnect rules (i.e. I have learned that Oracle uses opposite rules).

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

It should also round:

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

This round in the opposite direction I'm looking for:

=ROUND(-2.5,0)

EDIT: I said earlier that one type of rounding is more correct than another. After further research, I did not become more popular than the other. But I found an IEEE rounding rule , which, oddly enough, breaks ties by rounding to the nearest number. I am not looking for this method though :)

Answer 1

If it is always integers, this should work.

=FLOOR(A1+0.5,1)

If you need decimals, the number of decimals in B1 is as simple as

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

As Barry Houdini noted in the comments, FLOOR (, 1) is the same as INT (), but without an additional parameter.

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

Answer 2

The workaround may be as follows:

IF(A1 >= 0; ROUND(A1; 0); -ROUND(-A1; 0))

Answer 3

.

=IF(ISNUMBER(FIND(".5",A1)),ROUNDDOWN(A1,0),ROUND(A1,0))

Answer 4

, . , .

IF(A1 >= 0; ROUND(A1; 0); ROUND(ROUND(A1-0.999999; 0)+.999999; 0))