How to Round to Nearest 5 in Excel (3 Quick Ways)

MROUND() Function in Excel

Today I will be showing how you can round any number to its nearest multiple of 5 in Microsoft Excel.


Download Practice Workbook


How to Round to Nearest 5 in Excel


Let us have a data set like this. We have the record of average marks of the students of a school named Sunflower Kindergarten.

A Data set in Excel

Now the Principal of the school wants to round each mark to its nearest multiple of 5.

How can he execute that?

Here are the methods he can follow.


1.  Use ROUND and MROUND Function to Round to Nearest 5 in Excel


i. Using ROUND() Function

Syntax

=ROUND(number,num_digits)

For example,

ROUND(17.653,2) = 17.65

                     

ROUND(17.653,1)=17.7

  • If num_digit is equal to 0, it rounds the number to the nearest integer.

For example,

ROUND(17.653,0) = 18
  • If num_digit is less than 0, it rounds the number to the specified number of digits multiple of 10.

For example,

ROUND(17.653,-1) = 20

If you want to know more about the ROUND() function, visit this link.

Now, we come back to our problem. We want to round the average marks (column C) to the nearest multiples of 5.

In this case, the formula will be

=ROUND(C4/5,0)*5
  • Here ROUND(C4/5,0) rounds the quotient of C4 and 5 to its nearest integer.
  • Then we multiply it by 5 to get the nearest multiple of 5 around C4.

For example, if C4 is 79.65, ROUND(C4/5,0) returns ROUND(15.93,0)=16.

Then by multiplying it by 5, we get 80.

ROUND() Function in Excel

See we have rounded every mark to its nearest multiple of 5.


ii. Using MROUND() Function

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.

Syntax

=MROUND(number, multiple)
  • Takes two numbers as arguments, one is called number and the other is called multiple.
  • Rounds the number to the nearest multiple of the multiple.

For example,

MROUND(46.242)=46

                  

MROUND(48.345)=50
   

If you want to know more about the MROUND() function, visit this link.

So our formula will be

=MROUND(C4,5)

MROUND() Function in Excel

See, we have again rounded each mark to its nearest multiple of 5.


2. Use ROUNDUP and CEILING Function to Round to Nearest 5 in Excel

Now imagine a different scenario. The Principal wants to round each average mark to the nearest multiple of 5, but to the upper multiple.

For example, if the mark is 91.75, he wants it to round to 95, not 90.

How can he accomplish that?

You can use the ROUNDUP() and the CEILING() function of Excel.


i. Using ROUNDUP() Function

Syntax

=ROUNDUP(number, num_digits)
  • Takes two numbers as arguments. One is called number and the other is called num_digits.
  • Rounds the number to the number of digits specified by num_digits and greater than the number.
  • If num_digit is greater than 0, it rounds up the number to the specified number of digits after the decimal.

For example,

ROUNDUP(17.653,2) = 17.66

Read More: Excel 2 Decimal Places without Rounding (4 Efficient Ways)

                    

 ROUNDUP(17.653,1)=17.7

  • If num_digit is equal to 0, it rounds up the number to the nearest integer.

For example,

ROUNDUP(17.653,0) = 18
  • If num_digit is less than 0, it rounds up the number to the specified number of digits multiple of 10.

For example,

ROUNDUP(17.653,-1) = 20

If you want to know more about the ROUNDUP() function, visit this link.

Now, we come back to our problem. We want to round the average marks (column C) to the nearest upper multiple of 5.

In this case, the formula will be

=ROUNDUP(C4/5,0)*5
  • Here ROUNDUP(C4/5,0) rounds the quotient of C4 and 5 to its nearest upper integer.
  • Then we multiply it by 5 to get the nearest upper multiple of 5 around C4.

For example, if C4 is 79.65, ROUNDUP(C4/5,0) returns ROUNDUP(15.93,0)=16.

Then by multiplying it by 5, we get 80.

ROUNDUP() Function in Excel

See, we have rounded each mark to the nearest upper multiple of 5.


ii. Using CEILING() Function

You can achieve it by using the CEILING()  function of Excel too. This the most straightforward one.

Syntax

=CEILING(number, significance)
  • Takes two numbers as arguments, one is called number and the other is called significance.
  • Rounds the number to the nearest upper multiple of the significance.

For example,

CEILING(16.123,5) = 20

If you want to know more about the CEILING() function, visit this link.

Now, our formula will be

=CEILING(C4,5)

CEILING() Function in Excel

See, we have again rounded each mark to the nearest upper multiple of 5.


3. Use ROUNDDOWN and FLOOR Function to Round to Nearest 5 in Excel

Now imagine another different scenario. The Principal wants to round each average mark to the nearest multiple of 5, but to the lower multiple.

For example, if the mark is 84.75, he wants it to round to 80, not 85.

How can he accomplish that?

You can use the ROUNDDOWN() and the FLOOR() function of Excel.


i. Using ROUNDDOWN() Function

Syntax

=ROUNDDOWN(number, num_digits)
  • Takes two numbers as arguments. One is called number and the other is called num_digits.
  • Rounds the number to the number of digits specified by num_digits and less than the number.
  • If num_digit is greater than 0, it rounds down the number to the specified number of digits after the decimal.

For example,

ROUNDDOWN(17.653,2) = 17.65

                    

ROUNDUPDOWN(17.653,1)=17.6

  • If num_digit is equal to 0, it rounds down the number to the nearest integer.

For example,

ROUNDDOWN(17.653,0) = 17
  • If num_digit is less than 0, it rounds down the number to the specified number of digits multiple of 10.

For example,

ROUNDDOWN(17.653,-1) = 10

If you want to know more about the ROUNDDOWN() function, visit this link.

Now, we come back to our problem. We want to round the average marks (column C) to the nearest lower multiple of 5.

In this case, the formula will be

=ROUNDDOWN(C4/5,0)*5
  • Here ROUNDDOWN(C4/5,0) rounds the quotient of C4 and 5 to its nearest lower integer.
  • Then we multiply it by 5 to get the nearest lower multiple of 5 around C4.

For example, if C4 is 79.65, ROUNDUP(C4/5,0) returns ROUNDUP(15.93,0)=15.

Then by multiplying it by 5, we get 75.

ROUNDDOWN() Function in Excel

See, we have rounded each mark to the nearest lower multiple of 5.


ii. Using FLOOR() Function

You can use the FLOOR() function of Excel to accomplish this too. This is the simplest one.

Syntax

=FLOOR(number, significance)
  • Takes two numbers as arguments, one is called number and the other is called significance.
  • Rounds the number to the nearest lower multiple of the significance, less than it.

For example,

FLOOR(19.123,5) = 15

If you want to know more about the FLOOR() function, visit this link.

Now, our formula will be

=FLOOR(C4,5)

FLOOR() Function in Excel

See, we have again rounded each mark to the nearest lower multiple of 5.


Conclusion

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


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo