Prev: Axspreadsheet datasource
Next: Unhide Columns With A Password
From: Ron Rosenfeld on 3 Mar 2010 14:42
On Wed, 3 Mar 2010 10:07:16 -0500, "Gary Keramidas" <gkeramidas(a)XXMSN.com>
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
From: Joe User on 3 Mar 2010 16:56
"Ron Rosenfeld" <ronrosenfeld(a)nospam.org> 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" <ronrosenfeld(a)nospam.org> wrote in message
news:hdeto55qhir12jnc5p6lfim9bqpaivelt5(a)4ax.com...
> On Wed, 3 Mar 2010 10:07:16 -0500, "Gary Keramidas" <gkeramidas(a)XXMSN.com>
> 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

From: Rick Rothstein on 3 Mar 2010 17:18
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
news:OreBWxxuKHA.5812(a)TK2MSFTNGP02.phx.gbl...
> "Ron Rosenfeld" <ronrosenfeld(a)nospam.org> 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" <ronrosenfeld(a)nospam.org> wrote in message
> news:hdeto55qhir12jnc5p6lfim9bqpaivelt5(a)4ax.com...
>> On Wed, 3 Mar 2010 10:07:16 -0500, "Gary Keramidas"
>> <gkeramidas(a)XXMSN.com>
>> 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
>

From: Ron Rosenfeld on 3 Mar 2010 18:02
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
From: Joe User on 3 Mar 2010 18:57
"Ron Rosenfeld" <ronrosenfeld(a)nospam.org> 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" <ronrosenfeld(a)nospam.org> wrote in message
news:u9qto5pv05dsvjgkad9p4v1nl8hrjk1ak1(a)4ax.com...
> 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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Axspreadsheet datasource
Next: Unhide Columns With A Password