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.

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)`

- 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**. - If
**num_digit**is greater than 0, it rounds the**number**to the specified number of digits after the decimal.

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.

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)`

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.

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
**numbe**r 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)`

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.

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
**numbe**r 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)`

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

- Excel Round to 2 Decimal Places (with Calculator)
- How to Round Numbers in Excel Without Formula (3 smart ways)
- Excel Round to Nearest 100 (6 Quickest Ways)
- Excel round to nearest 10000 (5 Easiest Ways)
- How to Round Off Numbers in Excel (4 Easy Ways)
- 4 Easy Ways to Add Leading Zeros in Excel
- Using Excel to Round to Nearest 1000 (7 Easy Ways)
- Excel Custom Number Format Multiple Conditions
- How to Apply Accounting Number Format in Excel! (Easy way)