ROUND vs ROUNDUP vs ROUNDDOWN (vs Formatting)

The short answer to ROUND vs ROUNDUP: ROUND goes to the nearest value, ROUNDUP always goes up (away from zero), and ROUNDDOWN always goes down (toward zero). Formatting a cell with fewer decimals is a fourth option that only changes what you see, not the value Excel uses. This guide is for anyone working with prices, quantities or finance reports who needs totals that add up.

ROUND vs ROUNDUP vs ROUNDDOWN at a glance#

All three functions change the stored value. Formatting only changes the display.

How ROUND, ROUNDUP and ROUNDDOWN work#

All three take the same two parts: the number, and num_digits, the number of decimal places to keep.

=ROUND(number, num_digits)
=ROUNDUP(number, num_digits)
=ROUNDDOWN(number, num_digits)

• ROUND looks at the next digit. If it is 5 or more, the number rounds away from zero; if it is less than 5, it rounds toward zero.
• ROUNDUP ignores the next digit and always rounds away from zero.
• ROUNDDOWN ignores the next digit and always rounds toward zero, which simply cuts off the extra digits.

num_digits can be positive, zero or negative. Here is what ROUND does to 1234.567:

The ROUND function guide has more examples of each.

The same numbers rounded three ways#

Here are four unit costs from a supplier, rounded to two decimals:

With the cost in B2, the formulas are =ROUND(B2,2), =ROUNDUP(B2,2) and =ROUNDDOWN(B2,2). Look at the Mouse: 7.0011 is very close to 7, yet ROUNDUP still moves it to 7.01, because any extra digit at all sends it up. Excel shows 19.00 as 19 unless the cell is formatted to two decimals.

A shipping example#

An order of 130 units ships in boxes of 24. The plain division, =B1/B2, gives 5.4167. Each function answers a different question:

=ROUND(B1/B2,0)
=ROUNDUP(B1/B2,0)
=ROUNDDOWN(B1/B2,0)

ROUND returns 5, which would leave 10 units without a box. ROUNDUP returns 6, the number of boxes you need. ROUNDDOWN returns 5, the number of boxes you can fill completely. Picking the function is really picking the question.

ROUND vs formatting: why your totals look wrong#

Formatting changes what a cell shows. ROUND changes what it holds. The difference appears as soon as you add things up.

Put =1/3 in A2, A3 and A4, and format all three to two decimals. Each shows 0.33. Now add them:

=SUM(A2:A4)
=ROUND(A2,2)+ROUND(A3,2)+ROUND(A4,2)

The first returns 1, shown as 1.00, because Excel adds the full values (0.3333... three times). A reader adding up the visible 0.33s expects 0.99, so the report looks wrong. The second returns 0.99, because each value really is 0.33 before it is added.

Round each line or round the total?#

The same choice comes up on invoices. Say three items cost 9.99 each and tax is 8.25%. Tax on one item is 0.824175:

=ROUND(9.99*0.0825,2)*3
=ROUND(29.97*0.0825,2)

Rounding each line gives 0.82 three times, 2.46. Rounding the total tax gives 2.47. Neither is wrong, but they differ by a cent, so pick one rule and use it everywhere. If each line is printed on the invoice, round each line, so the printed lines add up to the printed total.

Use formatting when you only want a tidy display and the full precision is fine, such as averages in a dashboard. Use ROUND when the rounded value is the real value, such as a price you charge, a tax amount on an invoice or a figure that must match another system to the cent.

When to use ROUND#

Use ROUND when you want the nearest value and the stored number must change. It is the right choice for:

• Money, such as rounding a calculated price or tax to the cent: =ROUND(B2*C2,2).
• Reports that must add up on paper, line by line.
• Simplifying large numbers, such as rounding revenue to the nearest thousand: =ROUND(B2,-3).

Finance roles use this constantly; see the Excel skills for finance jobs.

When to use ROUNDUP or ROUNDDOWN#

Use ROUNDUP or ROUNDDOWN when the direction matters more than closeness.

Use ROUNDUP when falling short is not an option:

• Boxes, trucks, staff or rooms needed: you can't book 5.4 rooms.
• Shelf prices that must never be below cost: =ROUNDUP(B2,0) turns 12.3349 into 13.
• Billing time in blocks, such as charging every started hour: =ROUNDUP(B2,0) for hours worked.

Use ROUNDDOWN when you can only count complete units:

• Full boxes or full sets you can make from stock.
• Completed years, such as years of service from a decimal value.
• Cutting off extra digits without rounding, such as showing 18.99 instead of 19.00.

Common mistakes with ROUND, ROUNDUP and ROUNDDOWN#

These slips cause most rounding surprises:

1. Thinking formatting rounds. Decrease Decimal only hides digits. Sums and lookups still use the full value.
2. Leaving out num_digits. All three need it. Use 0 for whole numbers.
3. Rounding too early. If you round each step of a long calculation, small errors add up. Round once, at the step where the value becomes real, such as the final price.
4. Expecting ROUNDUP to go "higher" with negative numbers. ROUNDUP moves away from zero, so =ROUNDUP(-2.1,0) returns -3, a smaller number. ROUNDDOWN moves toward zero, so =ROUNDDOWN(-2.9,0) returns -2.
5. Mixing up ROUNDDOWN and INT. =INT(-2.9) returns -3, because INT always goes down to the next smaller whole number.
6. Using the wrong function for the question. ROUND gives 5 boxes for 130 units in boxes of 24, which leaves stock behind. Ask "nearest", "enough" or "complete" first.
7. Rounding a value that is still text. Numbers imported as text give #VALUE! or wrong results. Convert them to numbers first.

To round to a step other than a decimal place, such as the nearest 0.05, use CEILING to go up or FLOOR to go down: =CEILING(B2,0.05) turns 3.4567 into 3.5. When you fill any of these formulas down a column, the number cell should move with each row while a fixed setting, such as a step size in one cell, should stay locked; see relative vs absolute references. For more examples of each function, see the Excel ROUND functions explained.

Our recommendation#

Use ROUND for most everyday rounding, especially money, and round once at the point where the value becomes real. Use ROUNDUP when you need "enough", such as boxes or rooms, and ROUNDDOWN when you need "complete", such as full sets. Use formatting only when you want a tidier display and are happy for Excel to keep calculating with the full value.

In short: ROUND for nearest, ROUNDUP for enough, ROUNDDOWN for complete, formatting for looks only.

Practise it#

Try the ROUNDUP exercise on this page: 130 units in boxes of 24 needs 6 boxes, not 5. Then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet, including shelf prices that must never fall below cost.