Course / Dates, Rounding & Money

Rounding values (not just how they look)

ROUND, ROUNDUP, ROUNDDOWN and CEILING, and why formatting a number is not the same as rounding it. Beginner · 5 min · ROUND, ROUNDUP, ROUNDDOWN, CEILING, Format vs value

A number format with two decimals makes 0.516111 look like 0.52, but the cell still holds every digit, and every formula that uses it sees them all. To change the value itself, use a rounding function:

• ROUND(x, 2) rounds to the nearest cent (2 decimal places)
• ROUNDUP always rounds away from zero; ROUNDDOWN always rounds towards zero
• CEILING(x, 0.25) rounds up to the next multiple of 0.25

The sheet costs out a bakery's cookies, then packs a wholesale order.

Steps

1. Work out what one cookie costs. In D2:D6, divide the batch cost by the number of cookies in a batch.

2. Now round each cost to the nearest cent. In E2:E6, use =ROUND(D2, 2). The 2 means two decimal places.

3. A tasting box holds one of each cookie. Total both columns: SUM the costs in D7 and the rounded costs in E7.

4. Shop prices are the rounded cost times the markup in I1, rounded up to the next 25 cents (I2). In F2:F6, use =CEILING(E2*$I$1, $I$2).

5. Now pack the wholesale order. In B12, use ROUNDDOWN to count the boxes you can fill completely. In B13, use ROUNDUP to count the boxes you need for every cookie.

Remember

• Number formats change how a value looks; ROUND changes the value itself.
• Round before you total if the rounded amounts are what you charge or pay.
• ROUNDUP and ROUNDDOWN force the direction; CEILING and MROUND round to a multiple.

Previous: Working days and due dates · Next: Loan payments and savings growth