Hello,

Is there a way that I can convert a number to its nearest multiple of
5?

like in A1.. A5 i have

281
288
291
297
298

I want in B1 .. B5

280
290
290
295
300

That is up the number or down to nearest multiples of five

Thanks

You can use a formula like this...

=ROUND(A1/5,0)*5

Where 281 is in cell A1 it will return 280
--
HTH...

Jim Thomlinson


"Abdul" wrote:

Hello,

Is there a way that I can convert a number to its nearest multiple of
5?

like in A1.. A5 i have

281
288
291
297
298

I want in B1 .. B5

280
290
290
295
300

That is up the number or down to nearest multiples of five

Thanks

The MROUND function is what you want, you have to go to Tools, Addins, and
add the analysis toolpack if you don't already, then

=MROUND(A1,5)
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Abdul" wrote:

Hello,

Is there a way that I can convert a number to its nearest multiple of
5?

like in A1.. A5 i have

281
288
291
297
298

I want in B1 .. B5

280
290
290
295
300

That is up the number or down to nearest multiples of five

Thanks

Is there a way that I can convert a number to its nearest multiple of
5?

like in A1.. A5 i have

281
288
291
297
298

I want in B1 .. B5

280
290
290
295
300

That is up the number or down to nearest multiples of five

Use this...

=MROUND(A1,5)

and copy down.

Rick

Hey Abdul,

Homey!!!

VBRound can be used to round to the nearest "whatever" called with the
right argument of 5 it rounds to the nearest 5.

VBRound can be used from VBScript too.

Sub testVBRound()
v = Array(281, 288, 291, 297, 298)
For i = 0 To 4
MsgBox VBRound(v(i), 5)
Next i
End Sub

Function VBRound(a, b)
Result = ""
If 0 = b Then
Result = ""
ElseIf "" = b Then
Result = a
ElseIf 0 = a Then
Result = 0
Else
Result = b * ((a \ b) - CInt(((a Mod b) = (b / 2))))
End If
VBRound = Result
End Function

=ROUND(A1*2,-1)/2

Regards,
Bernd