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

The dataset showcases students’ average marks.

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

Steps:

• Select D5 and enter the formula.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: Round to Nearest 5 or 9 in Excel

Method 2 – Using the MROUND Function to Round Numbers to the Nearest multiple of 5 in Excel

Steps:

• Select D5 and enter the formula.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

Read More:  How to Round to Nearest 100 in Excel

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

To round each average mark to the nearest upper multiple of 5:

3.1 Using the ROUNDUP Function

Steps:

• Select D5 and enter the formula.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

3.2 Using the CEILING Function

Steps:

• Select D5 and enter the formula.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

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

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

To round each average mark to the nearest lower multiple of 5:

4.1 Using the ROUNDDOWN Function

Steps:

• Select D5 and enter the formula.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

4.2 Applying the FLOOR Function

Steps:

• Select D5 and enter the formula.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

Method 5 – Applying a VBA Code to Round Numbers to the Nearest Multiple of 5

Step 1:

• In the Developer tab, go to:

Developer → Visual Basic

• In the Microsoft Visual Basic for Applications, select Insert and choose Module:

Insert → Module

Step 2:

• In the Round to Nearest 5 module, enter the VBA code below

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

• To run the VBA code, click:

Run → Run Sub/UserForm

• Go back to your worksheet to see the output:

How to Round Numbers to the Nearest Integer in Excel

Use the ROUND function. Observe the image below.

Things to Remember

• You open the Microsoft Visual Basic for Applications window by pressing Alt + F11.
• If the Developer tab is not visible on the ribbon, enable it. Go to:

File → Option → Customize Ribbon

• The #N/A! error is displayed when the formula fails to find the referenced data.
• The #DIV/0! the error is displayed when a value is divided by zero(0) or the cell reference is blank.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Round to Nearest Whole Number in Excel
• How to Round Down to Nearest Whole Number in Excel
• Round Down to Nearest 10 in Excel
• How to Round to Nearest 10 Cents in Excel
• How to Round Off to Nearest 50 Cents in Excel
• Rounding to Nearest Dollar in Excel
• How to Round to Nearest 1000 in Excel

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