Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel is not rounding properly.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel is not rounding properly.
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel is not rounding properly.
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 , 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel is not rounding properly.
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel is not rounding properly.
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 , 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Get excel 2005 to line up damn dollar signs properly. Idiots. | Excel Discussion (Misc queries) | |||
Excel fails to save properly | Excel Discussion (Misc queries) | |||
Replace function not working properly in Excel 2000 SP3 | Excel Worksheet Functions | |||
The colors should work properly in Shared Excel Workbooks. | Excel Worksheet Functions |