ROUND, ROUNDUP and ROUNDDOWN practice exercises

Round numbers to a number of decimals, or to the nearest ten or hundred, and always up or down when the task needs it.

Start the first exercise

How ROUND, ROUNDUP and ROUNDDOWN work

ROUND, ROUNDUP and ROUNDDOWN all take two arguments: the number and how many decimals to keep. ROUND goes to the nearest value, ROUNDUP always moves away from zero, and ROUNDDOWN always moves toward zero. A negative number of decimals rounds to the left of the decimal point.

=ROUND(B2*1.25,2) adds 25% VAT to the price in B2 and keeps two decimals, so 19.99 becomes 24.99. =ROUNDUP(A2/B2,0) works out how many boxes you need: 25 items at 10 per box is 2.5, and it rounds up to 3. =ROUND(B2,-2) rounds 1,284 visitors to 1,300. =ROUNDDOWN(A2/B2,0) gives the number of full teams you can make.

The classic pitfall is a number format. Showing two decimals only changes how the cell looks, and later formulas still use the full value, so a column of rounded-looking prices can add up to something that seems off by a cent. Wrap the calculation in ROUND when the rounded value is what you want to work with. The exercises below cover a price, boxes, the nearest hundred and whole teams.

  1. 1Round a price to two decimalsIn C2, add 25% VAT to the price in B2 (multiply by 1.25) and use ROUND to round the result to 2 decimals.
  2. 2Always round up with ROUNDUPPremiumIn C2, work out how many boxes are needed for the items in A2 when each box holds B2. A part-full box is still a box, so use ROUNDUP with 0 decimals.
  3. 3Round to the nearest hundredPremiumIn C2, use ROUND to round the visitor count in B2 to the nearest hundred. A negative number of decimals rounds to the left of the decimal point.
  4. 4Count only whole teams with ROUNDDOWNPremiumIn C2, work out how many full teams of B2 people you can make from the A2 people who signed up. A team that isn't full doesn't count, so use ROUNDDOWN with 0 decimals.

Functions in this topic

Read the guide

Questions

How do I round to the nearest 5 in Excel?

Divide by 5, round, and multiply by 5 again: =ROUND(B2/5,0)*5 turns 23 into 25 and 22 into 20. The same trick works for any step. For the nearest 0.5, use =ROUND(B2/0.5,0)*0.5. For the nearest 10 or 100, a negative number of decimals is simpler.

Why does my total look off when the numbers are rounded?

Because a number format only changes what you see, not the value Excel stores. The total adds the unrounded numbers behind the cells. To make the total match what is on screen, round inside the formula with ROUND instead of formatting the cells.

What is the difference between ROUNDDOWN and INT?

They give the same result for positive numbers, but not for negative ones. ROUNDDOWN cuts off the decimals toward zero, so ROUNDDOWN(-2.5,0) is -2. INT always goes to the next lower whole number, so INT(-2.5) is -3. Use ROUNDDOWN when you need to keep a number of decimals as well.