ROUND vs ROUNDUP vs ROUNDDOWN in Excel and Google Sheets

I used INT on -2.1 as if it were ROUNDDOWN. It is not.

=ROUNDDOWN(-2.1,0)    → -2
=INT(-2.1)            → -3
=TRUNC(-2.1)          → -2
=ROUND(-2.1,0)        → -2
=ROUNDUP(-2.1,0)      → -3

ROUNDDOWN moved toward zero. INT moved to the next integer below, which for a negative number is farther from zero. Microsoft’s INT page says that explicitly, and this Sheets file did the same thing: INT(-1.5) was -2, INT(-2.9) was -3. TRUNC just chopped the fraction, so it landed with ROUNDDOWN on every integer case I ran, including the negatives. Microsoft’s TRUNC page draws the same contrast with INT.

That “up” and “down” language is also the whole difference between ROUNDUP and ROUNDDOWN, and it is easy to read it as “toward plus infinity” and “toward minus infinity.” It is not. Microsoft’s ROUNDUP page says away from zero; ROUNDDOWN says toward zero. On -2.9, ROUNDUP went to -3 and ROUNDDOWN went to -2. On 2.1, ROUNDUP went to 3 and ROUNDDOWN stayed at 2.

ROUND is the ordinary half-away-from-zero function in this file, not a bankers’ tie-to-even:

=ROUND(2.5,0)     → 3
=ROUND(1.5,0)     → 2
=ROUND(0.5,0)     → 1
=ROUND(-0.5,0)    → -1
=ROUND(-1.5,0)    → -2
=ROUND(-2.5,0)    → -3

If it had been banker’s rounding, 2.5 would have gone to 2 and -2.5 to -2. They did not. I did not test other halfway conventions, or MROUND, or FLOOR.MATH.

Digits, including to the left of the decimal

The second argument is the number of digits. Positive values are decimal places. Zero is an integer. A negative value moves left of the decimal point. 12.34 to -1 digit:

=ROUND(12.34,-1)        → 10
=ROUNDUP(12.34,-1)      → 20
=ROUNDDOWN(12.34,-1)    → 10

ROUNDUP jumps to 20 because that is the next multiple of ten away from zero. INT and TRUNC of 12.34 are still 12; they do not take a “tens” argument in the way I called them (INT has no digits argument, and I left TRUNC at the default).

One decimal place, for completeness:

=ROUND(2.25,1)        → 2.3
=ROUNDUP(2.25,1)      → 2.3
=ROUNDDOWN(2.25,1)    → 2.2
=ROUND(2.15,1)        → 2.2
=ROUNDUP(2.15,1)      → 2.2
=ROUNDDOWN(2.15,1)    → 2.1

2.15 rounded to 2.2 here. Binary floating-point can make .15 values misbehave in some workbooks; I am only reporting what this file returned, not a guarantee about every x.15.

The functions returned numbers, not text. I did not feed them text like "2.5", and I am not going to guess whether that would be coerced or come back #VALUE!. For actual #VALUE! and #NUM! cases in this workbook, see VALUE Error and NUM Error. Division that blows up is still a DIV/0 Error, and rounding will not save it.

If I need “always toward zero,” I use ROUNDDOWN or TRUNC. If I need “always away from zero,” ROUNDUP. If I need “the next integer down the number line, including through zero,” INT. ROUND is the everyday midpoint function, and in this file those midpoints went away from zero.