How to / Percentages and rounding

How to round to the nearest 5 in Excel

Use =MROUND(A2, 5). It rounds to the nearest multiple of the second number, up or down, whichever is closer.

To the nearest 5

ABC
1Value
22325
34.42
447
51234

In C2: =MROUND(A2, 5) → 25

23 is closer to 25 than to 20.

How it works

  1. MROUND finds the multiples of 5 on either side of the number and returns the nearer one.
  2. A number exactly halfway, such as 22.5, goes up.
  3. The second argument can be any step: 10, 100, 0.5 or 0.05.

Variations

  1. Always up to the next 5

    ABC
    1Value
    22325
    34.42
    447
    51234

    In C2: =CEILING(A2, 5) → 25

  2. Always down to the 5 below

    ABC
    1Value
    22320
    34.42
    447
    51234

    In C2: =FLOOR(A2, 5) → 20

  3. To the nearest 0.5

    ABC
    1Value
    2234.5
    34.42
    447
    51234

    In C2: =MROUND(A3, 0.5) → 4.5

  4. To the nearest 10

    ABC
    1Value
    22350
    34.42
    447
    51234

    In C2: =MROUND(A4, 10) → 50

  5. To the nearest 100

    ABC
    1Value
    2231200
    34.42
    447
    51234

    In C2: =ROUND(A5, -2) → 1200

    For tens, hundreds and thousands, ROUND with a negative number of digits does the same job.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related