Excel Round Up to Zero if Negative? Fix It

Errors & fixes · Updated November 22, 2026

No, Excel does not have a single built-in function that rounds negative numbers up to zero while rounding positive numbers normally. However, you can easily combine existing functions to achieve this: use =MAX(0, ROUNDUP(A1, 0)) for the standard behavior, or =IF(A1<0, 0, ROUNDUP(A1, 0)) when you need more control. The exact formula depends on whether you want negative numbers to become zero or to round away from zero (which is what ROUNDUP does by default).

What ROUNDUP Actually Does

Excel’s ROUNDUP function rounds a number away from zero. That means positive numbers round up (1.2 → 2), and negative numbers round down (‑1.2 → ‑2). It never returns zero unless the original number is exactly zero. So if you have a negative value like ‑5.7, ROUNDUP(-5.7, 0) gives ‑6, not 0. This is a common source of confusion: people expect “round up” to mean “toward positive infinity,” but Excel defines it as “away from zero.”

The Simplest Formula: MAX and ROUNDUP

To force any negative result to zero, wrap the ROUNDUP in a MAX function. The syntax is:

=MAX(0, ROUNDUP(A1, 0))

Here, A1 contains the number you want to round. The ROUNDUP part rounds the value to the specified number of digits (0 means whole number). Then MAX compares that result with 0 and returns the larger one. If the rounded result is negative, it returns 0; otherwise it returns the rounded positive number.

Example: if A1 = 3.2, the formula returns 4. If A1 = ‑3.2, ROUNDUP gives ‑4, but MAX(0, ‑4) returns 0. If A1 = 0, it returns 0.

Using IF for More Control

Sometimes you want to treat zero differently or apply different rounding rules. The IF version is more explicit:

=IF(A1<0, 0, ROUNDUP(A1, 0))

This checks if the value is negative. If yes, it returns 0. If not, it rounds up normally. This is useful when you want to preserve the original negative number in some other calculation, but for display or further use you want a non‑negative result. It also makes the logic clear to anyone reading the formula.

Alternatives: CEILING and MROUND

If you are rounding to a multiple (like nearest 5 or 10), consider CEILING. The function CEILING(number, significance) rounds up to the nearest multiple of significance. For example, CEILING(3.2, 1) gives 4, and CEILING(-3.2, 1) gives ‑3 (toward zero). To force negatives to zero, combine with MAX: =MAX(0, CEILING(A1, 1)). For rounding to nearest 5, use =MAX(0, CEILING(A1, 5)).

MROUND rounds to the nearest multiple, but it can round down or up depending on the remainder. It is not a direct substitute for ROUNDUP. If you need “round up to zero if negative” with a specific multiple, stick with CEILING.

Common Problems and Misconceptions

  • Misconception: “ROUNDUP always rounds toward positive infinity.” Actually, it rounds away from zero, so negatives get more negative.
  • Problem: Using ROUNDUP on a negative number and expecting 0. It never gives 0 unless the input is 0.
  • Problem: Forgetting that MAX returns the larger of two numbers, so if your rounded value is positive, it stays positive; if negative, it becomes 0.
  • Problem: Applying the formula to a cell that contains text – Excel returns a #VALUE! error. Ensure the cell contains a number.

How to Choose the Right Approach

Decide based on your data and intent:

  • If you want all negative numbers to become zero and positive numbers to round up to the nearest integer, use =MAX(0, ROUNDUP(A1, 0)).
  • If you want to preserve the original negative value but display zero in a report, use =IF(A1<0, 0, ROUNDUP(A1, 0)) – but note this changes the value, not just the display.
  • If you are rounding to a multiple (e.g., 0.5, 5, 10), use =MAX(0, CEILING(A1, 0.5)).
  • If you need to round to a specific number of decimal places, change the second argument in ROUNDUP, e.g., =MAX(0, ROUNDUP(A1, 2)) for two decimals.

Frequently Asked Questions

Does ROUNDUP ever return zero for a negative number?

No. ROUNDUP rounds away from zero, so a negative number always becomes more negative. The only way to get zero is to use MAX or IF as shown above.

What if I want negative numbers to round to zero, but keep the sign for positive numbers?

That’s exactly what the MAX formula does. It returns 0 for negatives and the rounded positive value for positives. If you want to keep the negative sign but round to zero (which is impossible), you’d need to rethink your logic.

Can I use this in a pivot table or chart?

Yes. You can add a calculated field in a pivot table using the same formula, or create a helper column in your source data. For charts, you can plot the helper column instead of the original data.

Does this work in Google Sheets?

Yes, the same functions exist in Google Sheets. The syntax is identical. The only difference is that in Google Sheets, you may need to use semicolons as argument separators if your locale uses them, but the formula logic is the same.

Why does my formula return #VALUE! error?

This usually happens when the cell contains text or an error value. Ensure the cell has a numeric value. Also check that you haven’t accidentally typed a comma instead of a period in decimal numbers.

Practical Example with a Small Table

Suppose you have a list of numbers in A2:A6: 3.2, -3.2, 0, -0.5, 7.8. In B2, enter =MAX(0, ROUNDUP(A2, 0)) and copy down. The results will be: 4, 0, 0, 0, 8. This shows how negative values become zero, positive values round up, and zero stays zero.

Mac and Excel for the Web Differences

The formulas work identically on Mac and in Excel for the web. The only difference is keyboard shortcuts: on Windows, press Ctrl+C to copy; on Mac, Cmd+C. There is no difference in function behavior.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.