Skip to content
Sheet ErrorSheet ErrorSheet Error

ROUNDUP on a negative number moves away from zero, not toward positive infinity

Updated

ROUNDUP(number, places) moves away from zero. ROUNDDOWN moves toward zero. ROUNDUP(1.2, 0) is 2. ROUNDDOWN(1.2, 0) is 1. Those match the everyday words “up” and “down.” ROUNDUP(-1.2, 0) is -2. The result is more negative. ROUNDDOWN(-1.2, 0) is -1, closer to zero. A pricing or tax rule that says “always round toward positive infinity” wants -1.2 to become -1. ROUNDUP will not do that. It did not malfunction. Away from zero and toward +∞ are the same direction only when the number is positive.

Sheet Error is an independent formula-error library, not a Google product. CEILING and CEILING.MATH on negatives, including a mode that changes direction, are CEILING vs MATH. FLOOR’s #NUM! when the signs disagree is FLOOR sign. A binary residue on ROUND itself is ROUND binary. Keep the Google Sheets UI in English.

Recreate it on a blank sheet

Section titled “Recreate it on a blank sheet”
  1. Open a blank spreadsheet. In A1 type n. A2 type 1.2. A3 type -1.2. A4 type -1.25.

Positive, the words match. In C1 type up. In D1 type down. In C2 enter =ROUNDUP(A2,0). C2 is 2. In D2 enter =ROUNDDOWN(A2,0). D2 is 1. Away from zero and toward zero. No error. This row is why a sheet of prices never shows the split.

Negative, away from zero. In C3 enter =ROUNDUP(A3,0). C3 is -2. In D3 enter =ROUNDDOWN(A3,0). D3 is -1. Same distance from zero as the positive row, signs flipped. Leave C3 as -2 and D3 as -1. Neither cell has a triangle. A column of refunds that “rounded up” became more negative.

C3 is -2 from =ROUNDUP(A3,0). A3 is -1.2. D3 is -1 from ROUNDDOWN. C2 is 2 and D2 is 1 from the positive 1.2. No error triangle.

One decimal place. In C4 enter =ROUNDUP(A4,1). C4 is -1.3. Away from zero at one decimal. In D4 enter =ROUNDDOWN(A4,1). D4 is -1.2. Toward zero. The extra hundredth moved only ROUNDUP.

Toward +∞ is not in these two cells. The next integer in the positive direction from -1.2 is -1. That is D3, ROUNDDOWN, on this particular value. It is not a reason to rename ROUNDDOWN to “up.” On the positive row, toward +∞ is C2, ROUNDUP. One English word, two functions, depending on the sign. =CEILING.MATH(A3,1,1) is the ceiling-function way to force a direction. Read that page before you swap it in. It is not a silent alias of ROUNDUP.

Negative places. In C5 enter =ROUNDUP(-15,-1). C5 is -20. Away from zero to the nearest ten. In D5 enter =ROUNDDOWN(-15,-1). D5 is -10. Toward zero. A positive ROUNDUP(15,-1) is 20. The sign split is the same at the tens place.

Leave C3 as -2.

What is actually wrong

Section titled “What is actually wrong”
  • “Up” in the function name means away from zero, not toward positive infinity. Positive samples hide the definition. Negative samples are the definition.
  • ROUNDDOWN is toward zero, not “toward −∞.” ROUNDDOWN(-1.2, 0) is -1, which is greater than the input. People who wanted the next integer down, -2, already have it in ROUNDUP on this value, and they will get the wrong side on the next positive row if they swap the names globally.
  • ROUND picks a nearest place and has its own halfway rule. It is not either of these. See ROUND binary when a halfway value is the surprise. C3 is not a halfway case. -1.2 is unambiguously away from zero at zero decimal places.
  • Exact integers do not move. ROUNDUP(-2, 0) stays -2. The split appears when there is a fraction.
  • FIXED rounds and returns text. It is not ROUNDUP. See FIXED is text.

Fix

Section titled “Fix”

Leave C3 as -2 and D3 as -1. They are the two definitions. Use another cell for the rule you meant.

Away from zero, including refunds that should become more negative. In F3 enter =ROUNDUP(A3,0). F3 is -2. Use this when a fractional unit always counts as a whole unit in the same direction as the sign.

Toward zero. In F4 enter =ROUNDDOWN(A3,0). F4 is -1. Use this when a partial unit never counts.

Toward positive infinity for both signs. For these two inputs, toward +∞ at zero decimals is 2 for 1.2 and -1 for -1.2. In F6 enter =IF(A3>=0, ROUNDUP(A3,0), ROUNDDOWN(A3,0)). F6 is -1. Check the positive row with the same formula. It is 2. Read it back. This IF is “away from zero when positive, toward zero when negative,” which matches toward +∞ for a step of 1. It is not the right construction for every place count and every tie. If the rule is really CEILING.MATH, use that function and its mode on purpose. See CEILING vs MATH.

F3 is -2 from ROUNDUP of -1.2. F4 is -1 from ROUNDDOWN. F6 is -1 from an IF that rounds toward positive infinity. C3 is still -2.

Do not wrap C3 in IFERROR. There is no error. The refund is one unit further from zero than the toward-zero rule. See IFERROR hides cause.

If ROUNDUP of a negative is more negative than ROUNDDOWN, both functions are following away-from-zero and toward-zero. If they differ on a positive row in the opposite way from the negative row, that is the same rule. If you needed every value to move toward +∞, ROUNDUP alone is the wrong name for the negative rows.