How to Round Numbers to the Nearest Multiple of 5 in Excel

Get FREE Advanced Excel Exercises with Solutions!

We use numbers in Excel in different cases. In those cases, we may get numbers with long decimals. But in most real cases, those long decimals are not that significant. We often curtail those numbers to make those numbers round to the nearest number. To become more accessible and easier to understand. In this article, we will discuss how you can round decimal numbers to the nearest multiple of 5 in Excel.

Let us have a data set like this. We have the record of the average marks of the students of a school named Sunflower Kindergarten. Now the Principal of the school wants to round each mark to its nearest multiple of 5. To do that, we will apply the ROUND, MROUND, ROUNDUP, CEILING, ROUNDDOWN, FLOOR functions, and the VBA code as well.

excel round to nearest 5


1. Applying ROUND Function to Round Numbers to the Nearest Multiple of 5

This section will apply the ROUND function to round to the nearest 5 in Excel. We want to round the average marks (column C) to the nearest multiple of 5. Let’s follow the instructions below to learn!

Steps:

  • First of all, select cell D5 and write down the below ROUND function in that cell. The functions are,
=ROUND(C5/5,0)*5
  • Hence, simply press Enter on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of the ROUND function. The return is 80.

Apply ROUND Function to Round to Nearest 5

  • Further, AutoFill the ROUND function to the rest of the cells in column D.

Apply ROUND Function to Round to Nearest 5

Read More: Round to Nearest 5 or 9 in Excel


2. Using MROUND Function to Round Numbers to the Nearest 5 in Excel

You can use the MROUND function to round the numbers to their nearest multiples of 5. This is the simplest method to achieve round-offs to certain multiples of any number. Let’s follow the instructions below to learn!

Steps:

  • Firstly, select cell D5 and write down the below MROUND function in that cell. The functions are,
=MROUND(C5,5)
  • Hence, simply press Enter on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of the MROUND function. The return is 80.

Use MROUND Function to Round to Nearest 5

  • Further, AutoFill the MROUND function to the rest of the cells in column D.

Use MROUND Function to Round to Nearest 5

Read More:  How to Round to Nearest 100 in Excel


3. Rounding Numbers to the Nearest Upper Multiple of 5 in Excel

Now imagine a different scenario. The Principal wants to round each average mark to the nearest multiple of 5, but the upper multiple. For instance, if the mark is 91.75, he wants it to round to 95, not 90. To do that, we will use the ROUNDUP and CEILING functions.

3.1 Inserting ROUNDUP Function

Now, we will apply the ROUNDUP function to round the nearest upper multiple. Let’s follow the instructions below to learn!

Steps:

  • First of all, select cell D5 and write down the below ROUNDUP function in that cell. The functions are,
=ROUNDUP(C5/5,0)*5
  • Hence, simply press Enter on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of the ROUNDUP function. The return is 80.

  • Further, AutoFill the ROUNDUP function to the rest of the cells in column D.


3.2 Using CEILING Function

In this sub-method, we will apply the CEILING function to round the nearest upper multiple. Let’s follow the instructions below to learn!

Steps:

  • Firstly, select cell D5 and write down the below CEILING function in that cell. The functions are,
=CEILING(C5,5)
  • Hence, simply press Enter on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of the CEILING function. The return is 80.

Use CEILING Function

  • Further, AutoFill the CEILING function to the rest of the cells in column D.

Read More: How to Round Numbers to Nearest 10000 in Excel


4. Rounding Numbers to the Nearest Lower Multiple of 5 in Excel

Now imagine yet another different scenario. The Principal wants to round each average mark to the nearest multiple of 5, but the lower multiple. For instance, if the mark is 84.75, he wants it to round to 80, not 85. To do that, we will use the ROUNDDOWN and FLOOR functions.

4.1 Using ROUNDDOWN Function

Now, we will apply the ROUNDDOWN function to round the nearest multiple. Let’s follow the instructions below to learn!

Steps:

  • Firstly, select cell D5 and write down the below ROUNDDOWN function in that cell. The functions are,
=ROUNDDOWN(C5/5,0)*5
  • Hence, simply press Enter on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of the ROUNDDOWN function. The return is 75.

  • Further, AutoFill the ROUNDDOWN function to the rest of the cells in column D.


4.2 Applying FLOOR Function

In this sub-method, we will apply the FLOOR function to round the nearest upper multiple. Let’s follow the instructions below to learn!

Steps:

  • Firstly, select cell D5 and write down the below FLOOR function in that cell. The functions are,
=FLOOR(C5,5)
  • Hence, simply press Enter on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of the FLOOR function. The return is 75.

  • Further, AutoFill the FLOOR function to the rest of the cells in column D.

Apply FLOOR Function


5. Applying VBA Code to Round Numbers to the Nearest 5

Now I’ll show you how to round to the nearest 5 in Excel by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will get round to the nearest 5 in Excel. Let’s follow the instructions below to learn!

Step 1:

  • First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

Run VBA Code to Round to Nearest 5

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Round to Nearest 5 will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Run VBA Code to Round to Nearest 5

Step 2:

  • Hence, the Round to Nearest 5 module pops up. In the Round to Nearest 5 module, write down the below VBA
Sub Round_to_Upper_Nearest_5()
SheetName = "VBA"
DataSet = "C5:C13"
Output = "D5:D13"
Set Input_Range = Worksheets(SheetName).Range(DataSet)
Set Output_Range = Worksheets(SheetName).Range(Output)
For i = 1 To Input_Range.Rows.Count
    For j = 1 To Input_Range.Columns.Count
        Number = Input_Range.Cells(i, j)
        If Int(Number / 5) = (Number / 5) Then
            Nearest_5 = Number
        Else
            k = 0
            While k < Number
                k = k + 5
            Wend
            Nearest_5 = k
        End If
        Output_Range.Cells(i, j) = Nearest_5
    Next j
Next i
End Sub

Run VBA Code to Round to Nearest 5

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Run VBA Code to Round to Nearest 5

  • After running the VBA Code, go back to your worksheet and you will be able to Round to Nearest 5 which has been given in the below screenshot.

Run VBA Code to Round to Nearest 5

Read More: Excel VBA: Round to Nearest 5


How to Round Numbers to the Nearest Whole Number in Excel

The ROUND function is an effective function to round numbers down to the nearest integer. In this function, we need to enter the number of digits to which our number argument will be rounded. The number will be rounded to the nearest integer if the value is 0. Have a look at the below screenshot to understand the nearest whole number in Excel.

Round to Nearest Whole Number in Excel


Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

👉 #N/A! the error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! the error happens when a value is divided by zero(0) or the cell reference is blank.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Using the above methods, we can round any number to a multiple of 5 quite sophistically. Do you know any other methods? Or are you facing any problems applying our methods? Let us know in the comment section.


Related Articles


<< Go Back to Round to Nearest Whole Number | Rounding in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo