# 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.
`=ROUND(C5/5,0)*5`
• 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.
`=MROUND(C5,5)`
• 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.
`=ROUNDUP(C5/5,0)*5`
• 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.
`=CEILING(C5,5)`
• Press Enter.

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

## 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.
`=ROUNDDOWN(C5/5,0)*5`
• 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.
`=FLOOR(C5,5)`
• 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.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF