Rounding to the nearest 5

[Lisa]

I need to round a group of numbers to the nearest 5...like 63 would be 65, 68
would be 70, 51 would be 50, etc. Is there a formula for this?

 

[bpeltzer]

rmound should do it (eg =mround(63,5) )

 

[bpeltzer]

The formula's okay, but the verbiage isn't... the function is mround (sorry!)

 

[Lisa]

If I have 100 different numbers, that will be very time consuming - unless I
am missing something. Is there a function that will recognize any number
and decide that it should be rounded to the nearest 5?

 

[Jim Thomlinson]

No there is no way to just have the entire sheet round all of the numbers to
the nearest 5. That being said you can just use cell references within the
formula so the amount of typeing should be minimal.

=mround(A1, 5)
where the cell you want rounded is in cell A1

Note that the above formula only works if you have the analysis toolpack
installed. If you try to use this or send it to someone who desn not have the
toolpack installed you will get errors. If that is a consideration then this
formula will work better

=round(A1/5, 0) * 5

 

[Don Guillett]

A macro?

Sub roundto()
For Each c In Range("c2:c12")
If IsNumeric(c) Then c.Value = Round(c / 5, 0) * 5
Next
End Sub