Excel is not rounding properly.

[HAH]

The proper way to round a number ending in 5 with nothing past it to the
previous digit is to round that digit to the nearest even number. Excel
doesn't do that; it uses the obsolete theory that always rounds up so 2.5,
which should round to 2, is being rounded to 3. This means that over time,
in financial uses, money will eventually be over-reported, and in scientific
uses, the amount of energy and matter will eventually be over-reported.
Current rounding theory is to round to the nearest even number so that,
statistically, half the time numbers will be rounded up and half the time
they will be rounded down, thus eliminating artificially high compounded
results.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

[Ron Coderre]

Time to check this website:

http://www.gomath.com/htdocs/lesson/round_lesson1.htm


***********
Regards,
Ron

XL2002, WinXP-Pro


"HAH" wrote:

> The proper way to round a number ending in 5 with nothing past it to the
> previous digit is to round that digit to the nearest even number. Excel
> doesn't do that; it uses the obsolete theory that always rounds up so 2.5,
> which should round to 2, is being rounded to 3. This means that over time,
> in financial uses, money will eventually be over-reported, and in scientific
> uses, the amount of energy and matter will eventually be over-reported.
> Current rounding theory is to round to the nearest even number so that,
> statistically, half the time numbers will be rounded up and half the time
> they will be rounded down, thus eliminating artificially high compounded
> results.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...lic.excel.misc

[JE McGimpsey]

There is no single "proper" way to round - the best unbiased method
depends on the distribution of numbers rounded.

For instance, if the method you suggested were employed, using =ROUND(x,
0) on a series that produced values on the interval (0, 1), say, the
result would be biased toward zero just as much as XL's ROUND() biases
toward 1. The "proper" method in that case would be to randomly or
alternately bias up and down.

However, to change the method used by XL's ROUND() now would break
existing workbooks that account for that method's bias.

See

http://support.microsoft.com/kb/196652/en-us

Note that VBA uses your method, which MS calls "Banker's Rounding"


In article <53A91AD5-64FE-4594-9BD5-4282734638C7@microsoft.com>,
HAH <HAH@discussions.microsoft.com> wrote:

> The proper way to round a number ending in 5 with nothing past it to the
> previous digit is to round that digit to the nearest even number. Excel
> doesn't do that; it uses the obsolete theory that always rounds up so 2.5,
> which should round to 2, is being rounded to 3. This means that over time,
> in financial uses, money will eventually be over-reported, and in scientific
> uses, the amount of energy and matter will eventually be over-reported.
> Current rounding theory is to round to the nearest even number so that,
> statistically, half the time numbers will be rounded up and half the time
> they will be rounded down, thus eliminating artificially high compounded
> results.

[Bernard Liengme]

Actually, the OP is correct is certain jurisdictions. I believe the legal
way to round in Australia is has OP states
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:51AA4157-1F49-4D96-9AA1-9C106FBC719E@microsoft.com...
> Time to check this website:
>
> http://www.gomath.com/htdocs/lesson/round_lesson1.htm
>
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "HAH" wrote:
>
>> The proper way to round a number ending in 5 with nothing past it to the
>> previous digit is to round that digit to the nearest even number. Excel
>> doesn't do that; it uses the obsolete theory that always rounds up so
>> 2.5,
>> which should round to 2, is being rounded to 3. This means that over
>> time,
>> in financial uses, money will eventually be over-reported, and in
>> scientific
>> uses, the amount of energy and matter will eventually be over-reported.
>> Current rounding theory is to round to the nearest even number so that,
>> statistically, half the time numbers will be rounded up and half the time
>> they will be rounded down, thus eliminating artificially high compounded
>> results.
>>
>> ----------------
>> This post is a suggestion for Microsoft, and Microsoft responds to the
>> suggestions with the most votes. To vote for this suggestion, click the
>> "I
>> Agree" button in the message pane. If you do not see the button, follow
>> this
>> link to open the suggestion in the Microsoft Web-based Newsreader and
>> then
>> click "I Agree" in the message pane.
>>
>> http://www.microsoft.com/office/comm...lic.excel.misc

[Jerry W. Lewis]

FWIW, the method advocated by the OP has been an ASTM standard for about 65
years, and is also specified by almost every other technical standards body
that gives a standard for rounding.

As a matter of historical interest,

1. I would greatly appreciate any information on how this came to be called
"bankers' rounding", since I have seen no evidence that bankers have ever
used it.

2. I would greatly appreciate any pre-1940's references to this method of
rounding. The only early reference that I have been able to nail down is the
1906 4th edition of Robert Woodward's "Probability & Theory of Errors" where
a statement on p.42 suggests that it was standard practice in the preparation
of numerical tables.

Jerry

"JE McGimpsey" wrote:

> There is no single "proper" way to round - the best unbiased method
> depends on the distribution of numbers rounded.
>
> For instance, if the method you suggested were employed, using =ROUND(x,
> 0) on a series that produced values on the interval (0, 1), say, the
> result would be biased toward zero just as much as XL's ROUND() biases
> toward 1. The "proper" method in that case would be to randomly or
> alternately bias up and down.
>
> However, to change the method used by XL's ROUND() now would break
> existing workbooks that account for that method's bias.
>
> See
>
> http://support.microsoft.com/kb/196652/en-us
>
> Note that VBA uses your method, which MS calls "Banker's Rounding"
>
>
> In article <53A91AD5-64FE-4594-9BD5-4282734638C7@microsoft.com>,
> HAH <HAH@discussions.microsoft.com> wrote:
>
> > The proper way to round a number ending in 5 with nothing past it to the
> > previous digit is to round that digit to the nearest even number. Excel
> > doesn't do that; it uses the obsolete theory that always rounds up so 2.5,
> > which should round to 2, is being rounded to 3. This means that over time,
> > in financial uses, money will eventually be over-reported, and in scientific
> > uses, the amount of energy and matter will eventually be over-reported.
> > Current rounding theory is to round to the nearest even number so that,
> > statistically, half the time numbers will be rounded up and half the time
> > they will be rounded down, thus eliminating artificially high compounded
> > results.
>