Multiples of 5

[beenay]

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

 

[ChrisUK]

Use mod instead

A1-mod(A1,5)

If A1=6 your answer would be 5
If A1=5 your answer would be 5
If A1=17 your answer would be 15

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

 

[marnie]

=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"

 

[Richie(UK)]

Hi,

Only tested this briefly, but how about:

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

where the number to be rounded is in A1.

HTH

 

[beenay]

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

 

[beenay]

Richie,

That did it!!! Thank you so much.

Thanks to the other suggestions too.

Brandon

 

[T. Cabin]

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.

 

[Aladin Akyurek]

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...