ROUND vs ROUNDUP vs ROUNDDOWN in Excel

Syntax: the same two arguments for all three

All three functions take the number and num_digits, and both arguments are required:

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

num_digits says where to round. A positive value counts decimal places: 2 rounds to hundredths, as for money. Zero rounds to a whole number. A negative value rounds to the left of the decimal point: −1 to tens, −2 to hundreds, −3 to thousands. On 1,234.567 that gives 1,234.57, 1,235 and 1,200 with ROUND, and 1,234.57, 1,235 and 1,300 with ROUNDUP. The number can be a cell, a constant or a whole calculation such as =ROUND(B2*C2,2).

How each one decides: nearest, away from zero, toward zero

ROUND looks only at the first digit that is dropped. If it is 5 or more, the kept part goes up in size; if it is 4 or less, the dropped digits are cut. So 4.35 becomes 4.4 and 4.349 becomes 4.3. Excel rounds halves away from zero, not to the nearest even digit as some statistics software and programming languages do.

ROUNDUP increases the size of the number whenever anything other than zeros is dropped: ROUNDUP(4.301, 1) is 4.4, while ROUNDUP(4.3, 1) stays 4.3 because nothing is lost. ROUNDDOWN simply cuts the extra digits: ROUNDDOWN(4.38, 1) is 4.3. The first block of the example shows all six combinations. ROUND and ROUNDUP agree for 4.35 and 4.38, ROUND and ROUNDDOWN agree for 4.32, and only ROUNDUP and ROUNDDOWN are fully predictable without looking at the digits.

Negative numbers, INT and TRUNC

Because ROUNDUP and ROUNDDOWN work on the size of the number, they mirror around zero: ROUNDUP(−4.32, 1) = −4.4 and ROUNDDOWN(−4.38, 1) = −4.3. That is usually what you want for amounts with a sign, such as credits and refunds, because +4.32 and −4.32 end up with the same size.

TRUNC does exactly what ROUNDDOWN does, but num_digits is optional and defaults to 0, so =TRUNC(-4.38) is −4. INT is different: it always goes to the next lower integer on the number line, so INT(−4.32) is −5 while TRUNC(−4.32) is −4. For positive numbers INT and TRUNC agree. If you need "toward plus infinity" or rounding to a multiple such as 0.05 or 15 minutes, use CEILING.MATH, FLOOR.MATH or MROUND, described in round to the nearest 0.05.

Worked example: three rounding functions side by side

Values, num_digits and the packing figures are inputs (yellow). The first block uses one decimal, the second varies num_digits on 1,234.567, the third shows why the choice matters for quantities.

Worked example: three rounding functions side by side

	A	B	C	D	E
1	Value	ROUND(…, 1)	ROUNDUP(…, 1)	ROUNDDOWN(…, 1)	INT(…)
2	4.32	4.3	4.4	4.3	4
3	4.35	4.4	4.4	4.3	4
4	4.38	4.4	4.4	4.3	4
5	-4.32	-4.3	-4.4	-4.3	-5
6	-4.35	-4.4	-4.4	-4.3	-5
7	-4.38	-4.4	-4.4	-4.3	-5
8
9	num_digits (on 1,234.567)	ROUND	ROUNDUP	ROUNDDOWN	TRUNC
10	2	1,234.57	1,234.57	1,234.56	1,234.56
11	0	1,235	1,235	1,234	1,234
12	-2	1,200	1,300	1,200	1,200
13
14	Packing 1,060 units in boxes of 48				Value
15	Units to ship				1,060
16	Units per box				48
17	Exact number of boxes				22.08
18	ROUND: boxes (too few, units are left behind)				22
19	ROUNDUP: boxes needed				23
20	ROUNDDOWN: completely full boxes				22
21	Units left over after the full boxes				4
22	Sum of three rounded thirds: 3 × ROUND(1/3, 2)				0.99

Fictional quantities. The functions behave the same in Excel, Google Sheets and LibreOffice for these examples.

Step by step: boxes, staff and other whole quantities

1,060 units ÷ 48 per box = 22.08 boxes. ROUND gives 22, because the decimal part is below 0.5, but 22 boxes hold only 1,056 units. ROUNDUP gives the correct answer for planning, 23 boxes. ROUNDDOWN answers a different question, how many boxes can be filled completely: 22, with =E15-E20*E16 = 4 units left for the last, partly filled box. =MOD(1060,48) returns the same remainder.

The same logic applies wherever a fraction of a unit is impossible. Staff needed for a workload, pallets, buses or print sheets are rounded up, because rounding down leaves work undone. Completed years of service, full months or whole packs that can be sold are rounded down. ROUND is the right choice for reporting a measured or calculated value as accurately as possible, not for deciding quantities.

Rounding the value vs formatting the display

Setting a cell to show two decimals with the number format only changes what you see. The cell still holds every digit, and later formulas use them. Three cells that each show 0.33 can sum to 1.00, because each holds 0.3333…. If each line is rounded with ROUND first, the sum is 3 × 0.33 = 0.99, the last row of the example. Both answers can be right; what matters is that you round at the step your rules require, for example per invoice line or once on the total, and do it with a formula so the stored value matches the printed one.

Avoid rounding too early in long calculations. Rounding an exchange rate or an interest rate before multiplying can change the final result by more than a cent. Keep full precision in intermediate cells and round once at the end, unless a rule says otherwise. How to keep rounded parts adding up to a fixed total is a separate technique, covered in the labs below.

Which one to use

Use ROUND for prices, results and reports where the nearest value is the fairest. Use ROUNDUP when a shortfall is not acceptable: quantities to order, capacity, minimum charges. Use ROUNDDOWN or TRUNC when only complete units count or when you must never overstate: full boxes, completed periods, cut-off decimals. Use INT when negative numbers must go to the next lower integer, as in some date and time calculations.

Common mistakes

• Expecting ROUNDDOWN to move negative numbers lower. ROUNDDOWN(−4.38, 1) is −4.3, toward zero. For the next lower value use INT or FLOOR.MATH.
• Using ROUND for quantities. ROUND(22.08, 0) = 22 boxes leaves units behind. Use ROUNDUP when the quantity must cover the need.
• Formatting instead of rounding. A number format hides digits but keeps them in calculations, so totals can differ from the visible figures.
• Rounding too early. Rounding intermediate rates or factors before multiplying changes the final result. Round once, at the step that requires it.
• Leaving out num_digits. ROUND, ROUNDUP and ROUNDDOWN need two arguments; =ROUND(A2) is rejected. Only TRUNC defaults to 0.
• Confusing the sign of num_digits. −2 rounds to hundreds, not to two decimals. ROUND(1234.567, −2) is 1,200.