Rounding

[Rich Mogy]

Hi All,
Excel has a function (mround), that returns the entered number rounded to
the number you enter, i.e

=mround(13,5) would return 15, because I told it to round 13 to the nearest
5.

I would like to create a function in VBA to do this. Any ideas?

Thanks in advance.

Rich

 

[Sandy Mann]

To do it with a Worksheet function without using MROUND() you would use
something like:

=ROUND(E23/5,0)*5

Do the same sort of thng in VBA.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rich Mogy]

Thanks -- that works.
Here's what I did

Function RoundToNearest(numin, roundto)
RoundToNearest = ((numin \ roundto) + 1) * roundto
End Function

 

[Sandy Mann]

That is actually rounding up to round to the nearest try:

Function RoundToNearest(numin, roundto)
RoundToNearest = Int(numin / roundto + 0.5) * roundto
End Function

You may also want to add:

Application.Volatile

at the start of the function.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

Is that function really doing what you want? First, you named it
RoundToNearest, but it actually should be called RoundUpTo. For example, use
11 for numin and 5 for roundto and you get 15 even though the nearest
multiple of 5 to 11 is 10. Also, when you set numin equal to 10 and roundto
equal to 5, your function returns 15 even though the "nearest" multiple of 5
to numin is itself... 10. Are these result really what you wanted? If not,
tells how you want your rounding to take place and we come up with a
function for you. Also, as a side question, can any of your numbers, either
numin or roundto, contain fractional elements to them?

Rick

 

[Rich Mogy]

Thanks Rick -- You are right -- it doesn't do what I want -- did with the
numbers I tested.

I replaced it with

RoundToNearest = Int(numin / roundto + 0.5) * roundto

And yes, numin could be a fraction, but roundto will always be an integer