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

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

Table of Contents

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.

Also read: Round to the Nearest Tenth in Excel

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:

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

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:

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

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.

Also read: How to Round Numbers in Excel Without 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:

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

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

Also read: How to Round Time to the Nearest Quarter Hour in Excel?

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:

  • How to Round to Nearest 100 in Excel?
  • How to Convert Decimal to Binary in Excel
  • How to Change Uppercase to Lowercase in Excel
  • How to Extract Number from Text in Excel (Beginning, End, or Middle)
  • How to Count Negative Numbers in Excel
  • How to Remove Leading Zeros in Excel
  • Find the Closest Match in Excel (Nearest Value)
  • Round Up to the Nearest Whole Number in Excel (Formulas)
Round UP or DOWN to Nearest 5 in Excel (Easy Formula) (2024)

FAQs

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

Round to the Nearest 5 in Excel Using the MROUND Function

The MROUND function in Excel allows you to round a number up or down to the nearest multiple of a given value. It works by taking two arguments: the number to be rounded and the multiple to round to. In our case, the multiple is five.

How to make Excel round to nearest 5? ›

Round to the Nearest 5 in Excel Using the MROUND Function

The MROUND function in Excel allows you to round a number up or down to the nearest multiple of a given value. It works by taking two arguments: the number to be rounded and the multiple to round to. In our case, the multiple is five.

How do you get Excel to round up or down? ›

To always round up (away from zero), use the ROUNDUP function. To always round down (toward zero), use the ROUNDDOWN function. To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function.

How to use ROUND function in Excel with formula? ›

For example, =ROUND (16.55, 1) will round 16.55 to 16.6. If num_digits is less than 0, the number will be rounded to the left of the decimal point (to the nearest 10, 100, 1000, and so on). For example, =ROUND (16.55, -1) will round 16.55 to the nearest 10 and return 20 as the return value or result.

How do you round up or down to the nearest multiple in Excel? ›

You can use the MROUND function to round to the nearest multiple and the FLOOR function to round down to a multiple.

What is the formula for rounddown? ›

For example, =ROUNDDOWN(A1,1) will round a number in A1 down to one decimal place, and =ROUNDDOWN(A1,0) will round down to the nearest whole number.

Do you round up or down with a 5? ›

Rounding is a process to estimate a particular number in a context. To round a number look at the next digit in the right place, if the digit is less than 5, round down and if the digit is 5 or more than 5, round up.

How to use round up formula? ›

For example, =ROUNDUP(7.18,0) will give you 8. If you use ROUNDUP with a negative number, the function gives you a more negative number. For example, =ROUNDUP(-3.118,0) gives you -4. If you input a negative number for num_digits, the function will round up to the left of the decimal point.

Is there a round up button in Excel? ›

Rounding Method #2: Use the Rounding Buttons

In the Home toolbar there are two buttons used to quickly round numbers within cells. The button to increase decimal places has several three zeros and an arrow pointing left. The button to decrease decimal places has three zeros and an arrow pointing to the right.

How do I use circular formula in Excel? ›

For circular formulas to work, you must enable iterative calculations in your Excel workbook. In Excel 2010 and higher, click File > Options, go to Formulas, and select the Enable iterative calculation check box under the Calculation options section.

How do I automatically round cells in Excel? ›

Set rounding precision
  1. Click File > Options.
  2. Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.
  3. Click OK.
  4. In the worksheet, select the cells that you want to format.
  5. On the Home tab, click the Dialog Box Launcher next to Number.

How do you round up or down to the nearest even number in Excel? ›

The EVEN function is used to round numbers up to the nearest even number. How do I use the EVEN function? To use the EVEN function, you must enter the formula into a cell in your worksheet. The formula should look like this: =EVEN (number).

How do you round up to the nearest 5 in sheets? ›

Rounding to the nearest 5 or 10 in Google Sheets can be done by using the round function. The round function takes two arguments, the number to be rounded and the number of decimal places to which it should be rounded.

How do you round to the nearest value in Excel? ›

To round up, down, or to an even or odd value

Select in an empty cell. On the Formulas tab, under Function, select Formula Builder. In number, type the number you are rounding up. In num_digits, type 0 to round the number up to the nearest whole number.

What is the rounding off rule for equal to 5? ›

If the digit to the right is less than 5, we do not change the rounding digit. However, all the digits to the right of the rounding digit are changed to 0. If the digit to the right is 5 or more than 5, we increase the rounding digit by 1, and all the digits to the right of the rounding digit are changed to 0.

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Jerrold Considine

Last Updated:

Views: 6101

Rating: 4.8 / 5 (78 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Jerrold Considine

Birthday: 1993-11-03

Address: Suite 447 3463 Marybelle Circles, New Marlin, AL 20765

Phone: +5816749283868

Job: Sales Executive

Hobby: Air sports, Sand art, Electronics, LARPing, Baseball, Book restoration, Puzzles

Introduction: My name is Jerrold Considine, I am a combative, cheerful, encouraging, happy, enthusiastic, funny, kind person who loves writing and wants to share my knowledge and understanding with you.