Course outline / Mathematical functions
Lesson 14 of 75 · Intermediate · 5 exercises
Rounding in depth
Round to the nearest 5, round up or down to a step, and cut off decimals.
What you'll practise
- Round to the nearest step with MROUND
- Always round up with CEILING
- Always round down with FLOOR
- Cut off the decimals with TRUNC
- INT and negative numbers
Round to the nearest step with MROUND
ROUND works with decimal places. MROUND rounds to the nearest multiple you choose, such as the nearest 5, 10 or 0.25.
=MROUND(number, multiple)
In cell D2, round the value in A2 to the nearest multiple of the step in B2.
Always round up with CEILING
CEILING rounds up to the next multiple, even if the number is only just over the last one.
=CEILING(number, multiple)
Use it when you can't have part of something, such as packs of 5.
In cell D2, round A2 up to the next multiple of B2.
Always round down with FLOOR
FLOOR is the opposite: it rounds down to the multiple below.
In cell D2, round A2 down to a multiple of B2.
Cut off the decimals with TRUNC
TRUNC simply removes the decimal part, without rounding. 17.99 becomes 17.
In cell D2, use TRUNC to keep only the whole-number part of A2.
INT and negative numbers
INT rounds down to the whole number below. For positive numbers that matches TRUNC, but for negative numbers "down" means further from zero: =INT(-3.7) is -4, while =TRUNC(-3.7) is -3.
In cell D2, use INT on the negative value in A3.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.