Level 2 · Restock numbers with ROUND, INT and ABS

Three small functions do most of the rounding work in a business sheet. They look alike, but each answers a different question, and picking the wrong one gives numbers that look fine and are wrong. The sheet lists the 17 Decor products that sold in October or November 2025, with units per month in B and C.

ROUND: a fixed number of decimals

=ROUND(number, num_digits)
=ROUND(C2/$I$1,1)

ROUND rounds to the nearest value with num_digits decimals, and halves go away from zero: ROUND(0.25,1) is 0.3 and ROUND(-0.25,1) is -0.3. In D3, Nantucket Brass Wall Mirror sold 70 units in 30 days: 2.333… becomes 2.3. Ridge Brass Bud Vase in D5 sold 80: 2.666… becomes 2.7.

num_digits can be 0 (whole numbers) or negative: ROUND(1234,-2) is 1200, handy for headline figures.

Rounding the value vs formatting it

The number format 0.0 on column D would also show 2.3. The difference is what the cell holds. A format changes only the display: the cell still holds 2.3333…, and anything that adds, compares or looks it up uses the full value. ROUND changes the value itself. When the rounded number is what the next person’s sheet should use, as with the buyers’ one-decimal rule here, round it. When you only want a tidy display, format it.

INT and TRUNC: whole units only

=INT(C2/$I$2)

The store only ships full cartons of 6. Nantucket sold 70 units, which is 11.67 cartons: 11 full ones and part of a 12th. ROUND would say 12, a carton you can’t fill. INT rounds down to the whole number below, so E3 shows 11. Birch Brass Floor Mirror sold 132 in E6, exactly 22 cartons, and Fairhaven Soy Wax Framed Print sold 4, so E14 shows 0.

TRUNC simply cuts off the decimals. For positive numbers it gives the same result as INT. They differ below zero:

Formula	Result	Why
=INT(11.67)	11	down to the next whole number
=TRUNC(11.67)	11	decimals cut off
=INT(-0.13)	-1	down, which below zero means away from zero
=TRUNC(-0.13)	0	decimals cut off, toward zero
=TRUNC(2.3333,1)	2.3	TRUNC also takes a number of decimals

Cedar Marble Bud Vase went from 13 units to 9: a daily change of (9 − 13) ÷ 30 = -0.13. INT calls that -1 unit a day, TRUNC calls it 0. Neither is “right”: decide which way the business wants a fraction to fall, and use ROUNDDOWN or ROUNDUP when you want that spelled out.

ABS: how big, ignoring the direction

=ABS(C2-B2)

Mia flags products that moved 20 units or more, up or down. C2-B2 is negative for products that fell, so a test like >=20 would miss a big drop. ABS returns the size of a number without its sign: Cedar Marble’s change of -4 becomes 4 in F2. Eight products moved by 20 or more, the biggest being Birch Brass Floor Mirror, up 82 units.

ABS also turns up in error checks: =ABS(actual-forecast) is the size of a forecast miss, and =ABS(a-b)<0.01 tests whether two totals agree to the cent.

Common mistakes

• Formatting instead of rounding when the rounded value is what matters downstream.
• ROUND for “how many complete units”: it rounds up past half and promises a carton you can’t fill. Use INT or ROUNDDOWN.
• Assuming INT and TRUNC agree on negatives. They don’t: INT(-0.13) is -1.
• Testing a signed change against a threshold. C2-B2>=20 only catches rises; wrap it in ABS.