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