ROUND、ROUNDUP、ROUNDDOWN の違い
INT(-2.1) を ROUNDDOWN と同じだと思って使ったが、違う。このシートでは ROUNDDOWN(-2.1,0) は -2、INT(-2.1) は -3、TRUNC(-2.1) は -2、ROUND は -2、ROUNDUP は -3。ROUNDDOWN はゼロに向かう。INT はその数以下の整数なので、負数ではゼロから遠ざかる。Microsoft の INT と TRUNC の説明も同じ。
要点
INT(-2.1) を ROUNDDOWN と同じだと思って使ったが、違う。このシートでは ROUNDDOWN(-2.1,0) は -2、INT(-2.1) は -3、TRUNC(-2.1) は -2、ROUND は -2、ROUNDUP は -3。ROUNDDOWN はゼロに向かう。INT はその数以下の整数なので、負数ではゼロから遠ざかる。Microsoft の INT と TRUNC の説明も同じ。
=ROUNDDOWN(-2.1,0) → -2
=INT(-2.1) → -3
=TRUNC(-2.1) → -2
=ROUND(-2.1,0) → -2
=ROUNDUP(-2.1,0) → -3確認のポイント
ROUNDUP はゼロから遠ざかり、ROUNDDOWN はゼロに向かう。「正の無限大へ」「負の無限大へ」ではない。-2.9 では ROUNDUP が -3、ROUNDDOWN が -2。2.1 では ROUNDUP が 3、ROUNDDOWN が 2。このシートの ROUND は中間値をゼロから遠ざける。銀行家の偶数丸めではない。2.5 は 3、-2.5 は -3 で、近い偶数にはならなかった。MROUND や FLOOR.MATH は試していない。
注意点と互換性
第 2 引数は桁数。正は小数桁、0 は整数、負は小数点の左側。12.34 を -1 桁にすると ROUND は 10、ROUNDUP は 20、ROUNDDOWN は 10。2.25 を小数 1 桁にすると ROUND と ROUNDUP は 2.3、ROUNDDOWN は 2.2。2.15 は、このシートでは ROUND が 2.2。INT に桁数引数はなく、ここでの TRUNC は既定のままなので 12.34 は 12 のまま。
英語の詳細例・数式・著者メモ
Excel ComparisonI 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) → -3ROUNDDOWN 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) → -3If 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) → 10ROUNDUP 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.12.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.