Round UP or DOWN to Nearest 5 in Excel (Easy Formula)

We often round numbers up or down to simplify them or make them easier to use. In some cases, we round numbers to a particular integer.

There might be situations where you can only express a number in multiples of 5.

In such cases, you might need to round the number up or down to the nearest 5.

In this tutorial, we will discuss how to use some of Excel’s in-built functions to round up down to the nearest 5.

The functions we will cover are:

  • MROUND
  • CEILING
  • FLOOR

The Logic in Rounding Numbers to the Nearest 5

When we say we are rounding a value to the nearest 5 or nearest 10, we mean that we are approximating the value to a multiple of 5. Rounding is usually done depending on which multiple of 5 or 10 is closest to the given value. 

So, if we have a number 24, we have the option to round it to 5 to either 20 (the lower bound) or 25 (the upper bound), since these are the two multiples of 5 that are the closest to the number 24.

The number 24 rounded to the nearest 5 is 25, because the upper bound 25 is closer to 24 than the lower bound 20.

Similarly, the number 22 rounded to the nearest 5 would be 20, because the lower bound 20 is closer to 22 than the upper bound 25.

The Logic in Rounding Up or Rounding Down Numbers

When we say we are rounding a value down to the nearest 5, we mean that we are approximating the value to the multiple of 5 that is nearest to but lower than the given value.

So rounding down the number 24 to the nearest 5 means approximating it to the lower bound 20, because that is the nearest multiple of 5 that is less than 24.

Similarly, rounding down the number 22 to the nearest 5 also means approximating it to the lower bound 20, because that is the nearest multiple of 5 that is less than 22.

And similarly, if we say that we need to round up to the nearest five, we take the number and increase it till it’s a multiple of 5 (so 21 will become 25 and 24 will also become 25)

Now that I have covered the logic or rounding up and down, let’s see some simple formulas that you can use in Excel to round down to the nearest 5 or 10.

Round to the Nearest 5 or 10 Using MROUND

The MROUND function lets you round a value to the nearest multiple of another value.

The syntax for the function is as follows:

MROUND(value, factor)

Here,

  • value is the number you want to round.
  • factor is the number, the multiple of which the value will be rounded to.

So if you want to round a number to the nearest 5, simply specify 5 as the second parameter.

Here are some examples to help you understand how the MROUND function rounds different values to the nearest 5:

MROUND function to round to nearest 5

Note: MROUND accepts both integer and decimal numbers as its parameters.

In case you want to round to the nearest 10, use 10 as the second argument in the formula.

Using the FLOOR Function to Round Down to the Nearest 5

While the MROUND function rounds to the nearest multiple of 5 or 10 (or whatever value you specify as the second argument), depending on whether the value is closer to the lower bound or upper bound, the FLOOR function always rounds the value down.

This means it always rounds the value to the nearest multiple of 5 or 10 that is less than the given value.

In other words, the FLOOR function basically works like the MROUND function, with the difference that it always rounds down.

The syntax for the FLOOR function is:

=FLOOR (value, significance)

Here,

  • value is the number that we want to round.
  • significance is the multiple that we want to round down to.

Here are some examples to help you understand how the FLOOR function rounds different values down to the nearest 5:

FLOOR function to round to nearest 5

Notice how the results differ from the results obtained using the MROUND function.

Also, in case you want to round down to any other significance (such as 10), use that as the second argument of the above FLOOR formula.

Using the CEILING Function to Round Up to the Nearest 5

If you rather want to round a value up to the nearest 5 instead of down, then you can use the CEILING function in Excel.

The CEILING function always rounds the value up. This means it always rounds the value to the nearest multiple of 5 that is higher than the given value.

The syntax for the CEILING function is:

=CEILING (value, significance)

 Here,

  • value is the number that we want to round.
  • significance is the multiple that we want to round up to.

Here are some examples to help you understand how the CEILING function rounds different values up to the nearest 5:

CEILING function to round up to the nearest 5

Notice how the results differ from the results obtained using the FLOOR and the MROUND functions.

How are MROUND, FLOOR/CEILING functions different from ROUND, ROUNDUP/ROUNDDOWN?

The main difference between these 2 groups of functions is that the ROUND, ROUNDUP, and ROUNDDOWN functions round a decimal value to the nearest 1 only, whereas the MROUND, FLOOR, and CEILING functions let you round to a given multiple of a number.

In this tutorial, I covered how you can use Excel’s built-in rounding functions like MROUND, FLOOR, and CEILING to round a value up or down to the nearest 5 or 10.

You can also use the same functions to round any value to a multiple of any other number.

In the end, we explained how these three functions are different from the ROUND, ROUNDUP, and ROUNDDOWN functions.

We hope this tutorial was helpful.

Other Excel tutorials you may also like: