rounding to nearest .5 in vba

[Gary Keramidas]

i'm wondering if there is a single formula that will round the following
examples in vba. i can do it with if statements, but looking for a more
compact solution to enter the value in a cell.

if it's < .5 it rounds to .5. if it's > .5 it rounds to the nearest .5.

4.571428571 5
0.571428571 0.5
0.575428571 1
0.285714286 0.5
0.214285714 0.5
57.03571429 57

 

[JLGWhiz]

Hi Gary, have you looked at the MROUND function. I did a quick test with it
and it will round to the nearest .5 of an integer.

Formula in cell B2: =MROUND(a1, .5)

 

[Rick Rothstein]

Why do you show 0.571428571 as rounding down to 0.5 rather than rounding to
1 (which I think is then nearest .5)... this would be equivalent to your
first number rounding to 5 and not 4.5.

 

[Paul C]

you can call the worksheet function MROUND like this

ActiveCell = WorksheetFunction.MRound(ActiveCell, 0.5)

of course you can use any variables you wantand do more stuff

NewNumber = WorksheetFunction.MRound(CurrentNumber, 0.5)

 

[Paul C]

you will likely need to use a couple of statments since your conditions as
described are not exactly the MROUND conditions.

MRound (.212485714,.5) would = 0

Your example also appears somwhat inconsistant since .571428571 yields .5
and .555428571 yields 1.

 

[Ron Rosenfeld]

i'm wondering if there is a single formula that will round the following
examples in vba. i can do it with if statements, but looking for a more
compact solution to enter the value in a cell.

if it's < .5 it rounds to .5. if it's > .5 it rounds to the nearest .5.

4.571428571 5
0.571428571 0.5
0.575428571 1
0.285714286 0.5
0.214285714 0.5
57.03571429 57

Gary,

Your examples seem inconsistent with your request, and your request is not
complete.

4.571428571 is closer to 4.5 than it is to 5
0.575428571 is closer to 0.5 than it is to 1

Note that the dividing point between 4.5 and 5.0 would be 4.75. So anything
between 4.5 and 4.75 should round to 4.5; anything above 4.75 should round to
5.0, if you are rounding to the *nearest 0.5*

And you did not define what you wanted to do in the event that your value fell
exactly on the midpoint.

The general formula, would be Round(n/0.5,0)*0.5

The VBA Round function rounds to the nearest even number. This provides a bit
more randomness in the rounding results than the Worksheetfunction.Round which
will round down if less than the 0.5 midpoint; but which will always round up
if *equal to* or greater than the 0.5 midpoint.

And there are more sophisticated methods of handling the mid-point issue.
--ron

 

[Joe User]

The VBA Round function rounds to the nearest even number.

That is not quite correct. For example, Round(2.xx,0) is 2 only when 2.xx
is exactly 2.50. Round(2.51,0) is 3.

This provides a bit more randomness in the rounding
results than the Worksheetfunction.Round

The benefit is arguable since the difference arises only at the precise
midpoint, a situation which I think is unlikely considering the Gary's
examples.


----- original message -----

 

[Rick Rothstein]

VBA uses the "round to even" (also known as Banker's Rounding) for rounding
numbers ending in 5 to the numerical position immediately in front of the 5
for **all** functions involving the need to round values (as in the Round,
Cxxx functions, Mod, etc.) with the **sole exception** of the Format
function... the Format function performs what I like to call "normal
rounding". So, while Round(2.5,0) becomes 2, Format(2.5,"#") becomes 3 (as
most of us expect it to<g>). This normal rounding works at all rounding
levels; so, for example, whereas Round(2.12345,4) becomes 1.1234,
Format(2.12345,"#.####") becomes 1.2345. To the best of my knowledge, the
Format function is the only function in VBA to use "normal rounding".

 

[Ron Rosenfeld]

That is not quite correct. For example, Round(2.xx,0) is 2 only when 2.xx
is exactly 2.50. Round(2.51,0) is 3.

I guess putting the formula example after defining the context for that
statement and before making that statement made it unclear that that statement
applied when the value fell exactly on the midpoint. I thought it was clear
that I was referring to midpoint issues, but I guess I'll have to be more
careful.


"And you did not define what you wanted to do in the event that your value fell
exactly on the midpoint.

"The general formula, would be Round(n/0.5,0)*0.5

"The VBA Round function rounds to the nearest even number."
--ron

 

[Joe User]

VBA uses the "round to even" (also known as Banker's Rounding)
for rounding numbers ending in 5 to the numerical position
immediately in front of the 5

I concur. My point was: you omitted the qualification "ending in 5"
previously.

(I wanted to make the same general comment myself, but I could not find a
concise way to express it. Well done!)

However, considering that Gary is rounding to 1 decimal place, I believe
there are only 5 decimal fractions out of perhaps 10^9 in Gary's examples
where "banker's rounding" would make a difference. I call that "unlikely"
;-).

So I would not make the case for using VBA Round instead
WorksheetFunction.Round based on a claim of "a bit more randomness in the
rounding results".

Instead, I might make the case for using VBA Round based on efficiency (I
ass-u-me), taking into consideration the fact that the difference is indeed
unlikely. I would explain the difference as a caveat, not as a feature ;-).

for **all** functions involving the need to round values
(as in the Round, Cxxx functions, Mod, etc.) with the
**sole exception** of the Format function

Good point. Also in the assignment of a floating-point number to an integer
variable (implicit rounding).


----- original message -----

 

[Joe User]

I thought it was clear that I was referring to midpoint issues [....]
"And you did not define what you wanted to do in the event
that your value fell exactly on the midpoint. [....]
"The VBA Round function rounds to the nearest even number."

I concur: I took your latter statement out of context. Mea culpa!

But I still think it is important to emphasize the unlikely difference that
"banker's rounding" makes in general, IMHO.

I think we can let the horse into heaven now.


----- original message -----

 

[Gary Keramidas]

thanks for all of the replies. i learned a few things about rounding, even
though some of my examples were incorrect.

 

[Bernd P]

Hello Gary,

I suggest to keep it simple:
result = int(input*2+0.5)/2 'round to nearest 0.5

Regards,
Bernd