Round to Nearest 5 or 9 in Excel (8 Easy Methods)

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.

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.

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.

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.

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.

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.

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.

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.

Round to Nearest 5 or 9.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!

Round to Nearest 5 or 9 in Excel (8 Easy Methods)

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

1. Combination of ROUND, CHOOSE and MOD Functions

2. Combining MROUND and MOD Functions

3. Merging IF, RIGHT and ROUND Functions

4. Using Formula with MOD Function

5. Incorporating IF, RIGHT and CEILING Functions

6. Combining IF with RIGHT Function in Excel

7. Using a Combination of CEILING and MOD Functions

8. Embedding VBA Code

Conclusion

Related Articles

Leave a reply Cancel reply