# Multiples of 5

#### beenay

##### New Member
I need to round a number to it's nearest multiple of 5. Example: 15 would equal 15, 16 would equal 15, 17 would equal 15, 18 would equal 20, 19 would equal 20, and 20 would equal 20.

CEILING and FLOOR don't work because they either always round up, or always round down.
This message was edited by beenay on 2002-10-11 12:14
This message was edited by beenay on 2002-10-11 12:20
This message was edited by beenay on 2002-10-11 12:27

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

A1-mod(A1,5)

Only problem would be if it was les than 5, then you get 0. If this was a problem you would have to test for 0 using if.

HTH

Chris

=MROUND(A1,5)

If cell A1 has a 2 in it, a zero will return, if A1 has a 3 in it, a 5 will return. You may need to choose an add-in option from the tools menu to make this work. The add-in is "Analysis ToolPak"

Hi,

Only tested this briefly, but how about:

=(ROUND((A1/5),0)*5)

where the number to be rounded is in A1.

HTH

MOD doesn't work because it always rounds down. 18 is returned as 15, when it should be returned as 20.

Richie,

That did it!!! Thank you so much.

Thanks to the other suggestions too.

Brandon

On 2002-10-11 12:27, beenay wrote:
MOD doesn't work because it always rounds down. 18 is returned as 15, when it should be returned as 20.

To use MOD, the formula would have to be something like :-

=IF(MOD(A1,5)<3,A1-MOD(A1,5),A1-MOD(A1,5)+5)

=MROUND(A1,5) and =ROUND((A1/5),0)*5 are better alternatives.

On 2002-10-11 12:31, beenay wrote:
Richie,

That did it!!! Thank you so much.

Thanks to the other suggestions too.

Brandon

Although I prefer myself the ROUND version, you should also check

=MROUND(A1,5)

Marnie proposed...

Replies
7
Views
226
Replies
5
Views
205
Replies
6
Views
793
Replies
2
Views
117
Replies
1
Views
204

1,219,961
Messages
6,151,167
Members
451,011
Latest member
OH650R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back