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 Keramidas
Excel 2003

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)


"Gary Keramidas" wrote in message
...
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 Keramidas
Excel 2003

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.

--
Rick (MVP - Excel)


"Gary Keramidas" wrote in message
...
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 Keramidas
Excel 2003

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)

--
If this helps, please remember to click yes.


"Gary Keramidas" wrote:

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 Keramidas
Excel 2003


.

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.
--
If this helps, please remember to click yes.


"Paul C" wrote:

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)

--
If this helps, please remember to click yes.


"Gary Keramidas" wrote:

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 Keramidas
Excel 2003


.

On Wed, 3 Mar 2010 10:07:16 -0500, "Gary Keramidas"
wrote:

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

"Ron Rosenfeld" wrote:
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 -----

"Ron Rosenfeld" wrote in message
...
On Wed, 3 Mar 2010 10:07:16 -0500, "Gary Keramidas"
wrote:

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

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".

--
Rick (MVP - Excel)


"Joe User" <joeu2004 wrote in message
...
"Ron Rosenfeld" wrote:
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 -----

"Ron Rosenfeld" wrote in message
...
On Wed, 3 Mar 2010 10:07:16 -0500, "Gary Keramidas"

wrote:

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

"Rick Rothstein" wrote:
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 -----


"Rick Rothstein" wrote in message
...
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".

--
Rick (MVP - Excel)


"Joe User" <joeu2004 wrote in message
...
"Ron Rosenfeld" wrote:
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 -----

"Ron Rosenfeld" wrote in message
...
On Wed, 3 Mar 2010 10:07:16 -0500, "Gary Keramidas"

wrote:

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

On Wed, 3 Mar 2010 13:56:09 -0800, "Joe User" <joeu2004 wrote:

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.

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

"Ron Rosenfeld" wrote:
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 -----

"Ron Rosenfeld" wrote in message
...
On Wed, 3 Mar 2010 13:56:09 -0800, "Joe User" <joeu2004 wrote:

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.

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

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

--


Gary Keramidas
Excel 2003


"Gary Keramidas" wrote in message
...
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 Keramidas
Excel 2003

Hello Gary,

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

Regards,
Bernd