Rounding is a commonly used process to eliminate the least significant digits to make it easier for communication and estimation processes. Excel provides different types of rounding. This tutorial will show you how to round to the nearest 5 or 9 in Excel.

**Table of Contents**hide

## How to Round Numbers to Nearest 5 or 9 in Excel: 8 Easy Methods

To round a number to the nearest 5 or 9, there are a total of eight methods. Seven of them are different formulas originating from different combinations of different functions. And another one is a VBA code to create a custom function that directly rounds up a number to the nearest 5 or 9. The following dataset will be used to demonstrate all of the methods.

### 1. Combination of ROUND, CHOOSE and MOD Functions

The first formula we will be using is a combination of the **ROUND****, ****CHOOSE****, **and **MOD** functions.

To round a number, we use the **ROUND **function for a specified number of digits. It needs two arguments, a number it is rounding and the number it is rounding to. Whereas the **CHOOSE **function takes an index number and several numbers as arguments and chooses a specific action according to the index number. The **MOD **function takes a number and a divisor to return the remainder.

**Steps:**

- First select cell
**C5**. - Then write down the following formula in the cell.

`=ROUND(B5,0)+CHOOSE(MOD(ROUND(B5,0),10)+1,-1,-2,3,2,1,0,-1,2,1,0)`

- After that, press
**Enter**on your keyboard. You will have the value rounded to the nearest 5 or 9.

- Now select the cell again. Finally, click and drag the fill handle icon down to fill up the rest of the column with the formula.

Thus you will have the numbers rounded to the nearest 5 or 9 using the formula.

🔍 **Breakdown of the Formula**

`👉`

The **ROUND(B5,0) **takes the value of cell **B5 **and rounds up the value if it is a fraction. It returns 13.

`👉`

The **MOD(ROUND(B5,0),10) **returns the remainder of the previous value and 10, which is 3.

`👉`

Then **CHOOSE(MOD(ROUND(B5,0),10)+1,-1,-2,3,2,1,0,-1,2,1,0) **chooses the number to be added based on the remainder and original value. In this case, it is 2.

`👉`

Finally, **ROUND(B5,0)+CHOOSE(MOD(ROUND(B5,0),10)+1,-1,-2,3,2,1,0,-1,2,1,0) **adds the value from the previous function and adds it with the original one.

**Read More: **How to Round Numbers to the Nearest Multiple of 5 in Excel

### 2. Combining MROUND and MOD Functions

The next formula uses a combination of **MROUND** and **MOD** functions to round a number to the nearest 5 or 9.

We use the **MROUND **function to return a number rounded up to desired multiple. To begin with, this function can take two arguments- the number and the multiple. The **MOD **function takes a number and a divisor to return the remainder.

**Steps:**

- First, select cell
**C5**. - Now, write the following formula in the cell.

`=MROUND(B5,5)-((MOD(MROUND(B5,5),10))=0)`

- After that, press
**Enter**.

- Now select the cell again. And then click and drag the fill handle icon to fill out the rest of the column.

As a result, you will have all the numbers rounded up to the nearest 5 or 9.

🔍 **Breakdown of the Formula**

`👉`

**MROUND(B5,5) **round the value of cell **B5 **to a multiple of 5 and returns 15.

`👉`

**MOD(MROUND(B5,5),10) **returns the remainder of 15 and 10, which is 5.

`👉`

**(MOD(MROUND(B5,5),10))=0 **returns a boolean value, depending on the remainder is 0 or not. In this case, it is FALSE.

`👉`

Finally, **MROUND(B5,5)-((MOD(MROUND(B5,5),10))=0) **returns after subtracting either 0 or 1 and results in a value rounded to 5 or 9.

**Read More: **How to Round to Nearest Whole Number in Excel

### 3. Merging IF, RIGHT and ROUND Functions

In this case, we will be going to use the combination of **IF****, ****RIGHT****, **and **ROUND** functions.

First, the **IF **function takes three arguments- a condition, a value if the condition is true and a value if that is false. Second, the **RIGHT **function accepts two arguments- a string and a number. Then it extracts those numbers from the right side of the string. And we use the **ROUND **function to round a number. It accepts two arguments, a number it is rounding and the number it is rounding to.

**Steps:**

- First, select cell
**C5**. - Now, write down the following formula in the cell.

`=IF(B5>ROUND(B5,-1),IF(RIGHT(B5)="1",ROUND(B5,-1)-1,ROUND(B5,-1)+5),IF(RIGHT(B5)="0",ROUND(B5,-1)-1,IF(RIGHT(B5)<"7",ROUND(B5,-1)-5,ROUND(B5,-1)-1)))`

- Then press
**Enter**.

- Next, select the cell again. And finally, click and drag the fill handle icon bar to fill out the rest of the column with the formula.

This is how you round a number to the nearest 5 or 9 in Excel with this formula.

🔍 **Breakdown of the Formula**

`👉`

The **RIGHT(B5) **takes the last digit of the value in cell **B5**.

`👉`

First, the **ROUND(B5,-1) **function round the value in cell **B5 **to the nearest multiple of 10. In this case, it is 10.

`👉`

Then **IF(B5>ROUND(B5,-1),…) **checks whether the value is greater than the rounded number or not.

`👉`

If it is, it moves on to the **IF(RIGHT(B5)=”1″,ROUND(B5,-1)-1,ROUND(B5,-1)+5) **section, where it checks the last digit is 1. At this point, if it is 1, then it subtracts 1 from the round value, else it adds 5 to the rounded value.

`👉`

Now **IF(RIGHT(B5)=”0″,ROUND(B5,-1)-1,…) **comes into play if the first **IF **function’s condition was false. It first checks whether the last digit is 0 or not. If it is, then 1 is subtracted from the rounded value, else it moves on to the next **IF **function below.

`👉`

Finally, **IF(RIGHT(B5)<“7”,ROUND(B5,-1)-5,ROUND(B5,-1)-1) **comes into play if all of the conditions were false. This function first checks whether the last digit is less than 7. If it is, then 5 is subtracted from the rounded value, else 1 is subtracted.

All these nested **IF **loops combined with other functions finally give our result.

**Read More: **Round Down to Nearest 10 in Excel

### 4. Using Formula with MOD Function

This formula is quite short. But has a lower accuracy when a range of values to be rounded is wide. The formula only uses **the MOD function**. We use this function to find out the remainder of a division operation. To begin with, the function takes the number to be divided and the divisor as its arguments.

**Steps:**

- First, select cell
**C5**. - Then write down the following formula in the cell.

`=B5-MOD(B5,10)+4*(MOD(B5,10)>5)+5`

- Now press
**Enter**.

- After that, select the cell again and click and drag the fill handle icon to fill up the rest of the cells.

This way you can round the numbers to the nearest 5 or 9 in Excel with the help of this formula.

🔍 **Breakdown of the Formula**

`👉`

First, **MOD(B5,10) **returns the remainder of the value when cell **B5 **is divided by 10. It returns 3.

`👉`

Next, **(MOD(B5,10)>5) **returns false as the value is smaller than 3.

`👉`

And **MOD(B5,10)+4*(MOD(B5,10)>5) **returns 3 as the result of the algebraic calculation.

`👉`

Finally, **B5-MOD(B5,10)+4*(MOD(B5,10)>5)+5 **subtracts 3 from the value of cell **B5**.

**Read More: **How to Round to Nearest 100 in Excel

### 5. Incorporating IF, RIGHT and CEILING Functions

This formula is a combination of **IF, OR, RIGHT, MAX, **and **CEILING **functions.

The **IF **function checks a condition and returns two different values depending on the boolean value of the condition. It takes the three as arguments.

Similarly, The **OR **function checks a condition. But only returns whether that is true or false. The **RIGHT **function takes some characters from the right side of the string value. In the case of the **MAX **function, it returns the maximum number between several numbers. The **CEILING **function rounds up a value to the nearest integer or multiplication of a value. This function takes these two arguments- the number it is rounding and the significance.

**Steps:**

- First, select cell
**C5**. - Now enter the following formula in the cell.

`=IF(OR(RIGHT(B5,1)={"0","1","2","3","4"}),MAX(CEILING(B5+1,5),0),MAX(CEILING(B5+1,10)-1,0))`

- After that, press
**Enter**.

- Finally, select the cell again and click and drag the fill handle icon to the end of the column to fill up the rest of the cells with the formula.

As a result, the formula will round up the values to the nearest 5 or 9 in Excel.

🔍 **Breakdown of the Formula**

`👉`

**RIGHT(B5,1) **takes the first value from the right of cell **B5 **which is 3.

`👉`

Next **OR(RIGHT(B5,1)={“0″,”1″,”2″,”3″,”4”}) **returns if the value matches from the list. In this case, it is TRUE.

`👉`

**CEILING(B5+1,5) **returns the value 15.

`👉`

**MAX(CEILING(B5+1,5),0) **returns if the final condition of the **IF **function is true. Its value of it is 15.

`👉`

**MAX(CEILING(B5+1,10)-1,0) **returns if the final condition of the **IF **function is false. The value for cell **B5 **of the function is 19.(It is printed in this case).

`👉`

Finally, **IF(OR(RIGHT(B5,1)={“0″,”1″,”2″,”3″,”4”}),MAX(CEILING(B5+1,5),0),MAX(CEILING(B5+1,10)-1,0)) **returns one of the last two functions as a result which will be the nearest 5 or 9 of the original value. In this case, it is 15.

**Read More: **How to Round to Nearest 1000 in Excel

### 6. Combining IF with RIGHT Function in Excel

This formula consists of the **IF **and **RIGHT **functions.

The **IF **function checks a condition and returns two different values depending on the boolean value of the condition. It takes the three as arguments. Whereas the **RIGHT **function takes some digits from the right of a value.

**Steps:**

- First of all, select cell
**C5**. - Then write down the following formula in the cell.

`=B5-RIGHT(B5,1)*1+IF(RIGHT(B5,1)*1<6,5,9)`

- Now press
**Enter**on your keyboard.

- Next, select the cell again. Then click and drag the fill handle icon to fill up the rest of the cells of the column with the formula.

This is how you round a number to the nearest 5 or 9.

🔍 **Breakdown of the Formula**

`👉`

**RIGHT(B5,1) **takes the right digit from cell **B5 **which is 3.

`👉`

**RIGHT(B5,1)*1 **also returns the value 3.

`👉`

Now **IF(RIGHT(B5,1)*1<6,5,9) **returns the value 5 here as the condition **RIGHT(B5,1)*1 **is TRUE.

`👉`

Finally, **B5-RIGHT(B5,1)*1+IF(RIGHT(B5,1)*1<6,5,9) **returns the value 15 after all the algebraic calculations.

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

### 7. Using a Combination of CEILING and MOD Functions

This formula consists of the **CEILING **and **MOD **functions. The **CEILING** function accepts two arguments- the number it is rounding and the significance. It returns the rounded-up value to the significance or a multiple of it. The **MOD **function takes two numbers as arguments and returns the remainder of the first number divided by the second.

**Steps:**

- First, select cell
**C5**. - Second, write down the following formula in the cell.

`=CEILING(B5+(MOD(B5,2)=0),5)-(MOD(CEILING(B5+(MOD(B5,2)=0),5),2)=0)`

- Now press
**Enter**on your keyboard.

- Next, select the cell again. Now, click and drag the fill handle icon to fill up the rest of the cells with the formula.

This way you can round up a number to the nearest 5 or 9.

🔍 **Breakdown of the Formula**

`👉`

**MOD(B5,2) **returns the remainder when the value of cell **B5 **is divided by 2. In this case, it is 1.

`👉`

**MOD(B5,2)=0 **returns a boolean value of whether the remainder is 0 or not. In this case, it is FALSE as the remainder was 1.

`👉`

**CEILING(B5+(MOD(B5,2)=0),5) **rounds the original value up to a multiple of 5 and returns 15.

`👉`

**MOD(CEILING(B5+(MOD(B5,2)=0),5),2) **returns the remainder of when the previous value is divided by 2.

`👉`

**MOD(CEILING(B5+(MOD(B5,2)=0),5),2)=0 **returns a boolean value depending on whether the value of the remainder is equal to 0 or not.

`👉`

Finally, the result of the algebraic operations in **CEILING(B5+(MOD(B5,2)=0),5)-(MOD(CEILING(B5+(MOD(B5,2)=0),5),2)=0) **returns the value which contains the nearest 5 or 9.

**Read More: **Rounding to Nearest Dollar in Excel

### 8. Embedding VBA Code

Instead of all the complex and large formulas you can easily make your single function with Microsoft Visual Basic for Applications (VBA) which can also round up a number to the nearest 5 or 9 in Excel. This is particularly helpful if you want to reuse the code again and again.

But first, you need to have the **Developer **tab shown on your ribbon. **Enable the Developer tab** if you don’t have it in your Excel ribbon. Once you have that follow these steps to make your own function in VBA.

**Steps:**

- First of all, go to the
**Developer**tab on your ribbon and select**Visual Basic**from the**Code**group.

- As a result, the VBA window will open up. Now click on
**Insert**and select**Module**from the drop-down menu.

- Next, go to the inserted module and write down the following formula.

```
Function Round59(number As Double)
Dim N As Single, M As Single
N = Int(number / 10) * 10
M = number - N
If M >= 2 And M < 7 Then
M = 5
Else
If M >= 7 Then
M = 9
Else
M = 9
N = N - 10
End If
End If
Round59 = N + M
End Function
```

- Now save and close the window.
- After that, go back to the spreadsheet and select cell
**C5**. - Then write down the following formula.

`=Round59(B5)`

- After that, press
**Enter**.

- Now, select the cell again. Then click and drag the fill handle icon to the end of the column to fill up the rest of the cells with the formula.

Henceforth, you can use the formula anywhere in that Excel workbook to round a number to the nearest 5 or 9.

**Read More: **Excel VBA: Round to Nearest 5

**Download Practice Workbook**

Download the workbook with spreadsheets containing all the formulas and the VBA code below.

## Conclusion

These were all the formulas and the VBA code to round a value to the nearest 5 or 9 in Excel. Hope you have found this guide helpful and informative. If you have any questions or suggestions, let us know below. For more detailed guides like this visit Exceldemy.com.