Rounding of numbers
Rounding of numbers
(OP)
In rounding to, say, one digit, when given the choice of rounding up or down, excel seems to always round up. Say, 24.55 to 24.6 and 24.45 to 24.5. We've always used the convention of rounding to the even number. Any reason excel does this?





RE: Rounding of numbers
TTFN
RE: Rounding of numbers
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: Rounding of numbers
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Rounding of numbers
I assume the comment applies to rounding that occurs in formatting of dislay of numbers. (not when you use a function).
As was mentioned above, statistically speaking, if there is no info beyond the 5, there should be no preference to round up over rounding down. Therefore if you took a very large set of sample data with exactly 2 decimal places and displayed as rounded to 1 decimal place using excel’s display convention (5’ s rounded up), the mean of the DISPLAYED data would increase slightly since all the 5’s are rounded up (only half should be rounded up).
Fortunately, we usually do calculations referencing the ACTUAL cell value (not displayed value)...which provides all the decimal places and is unaffected by display settting. (But if someone calculated mean from displayed value it would be higher).
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding of numbers
And 5, rounding up, does split the field in half:
0 through 4.999 round down. 5.000 through 9.999 round up. You can see that the interval is split evenly.
I'm still not clear what "rounding to the even number" is supposed to do for you. Based on quantization error analysis, rms error is twice the precision divided by sqrt(12). That means that rounding to even integers doubles the rms quantization error. Accountants that somehow believe otherwise are confused about math.
TTFN
RE: Rounding of numbers
By rounding the 5.000 to the nearest even number, then you'll have the same number values being rounded up (0.001 to 4.999) and rounded down (5.001 to 9.999), with half the 5.000's going up, and half the 5.000's going down, assuming of course, a uniform distribution.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: Rounding of numbers
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: Rounding of numbers
> The statistical error is increased, nonetheless
> The interval chosen is not correct. If the x.500 to x+1.499 values are evenly distributed, there is no bias toward any number. Just because the x.000 values don't get rounded, doesn't mean that it shouldn't be counted. Typical greedy banker mentality.
TTFN
RE: Rounding of numbers
http://home.houston.rr.com/electricpete/Round5.xls
Consider (as in attached spreadsheet) the set of original data
11, 12, 13, 14...97, 98, 99
The average of the data is 55
If we round to nearest 10's, using a simple round-up rule for 15, 25, 35, 45, then we get a set of data whose average is 54.494 (change of ~ 0.5 from original data)
If we round to nearest 10's using even-multiple-of-10 roundup rule for 15, 25, 35 (i.e. 15 rounds to 20, 25 rounds to 20, 35 rounds to 40 45 rounds to 40 etc), then we get a set of data whose average is 55.06 (change of ~0.06 from the original data... much closer to original data).
So the second approach provides better preservation of the mean of the data. The first roundup rule tends to increase the mean when the original data has entries ending in exactly 5. Note for irrational numbers, there is no concern because the numbers don't end in exactly 5... they keep on going. Also not a concern when the rounding is for display and not used in calculations.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding of numbers
http
If we round to nearest 10's, using a simple round-up rule for 15, 25, 35, 45, then we get a set of data whose average is 55.505 (change of ~ 0.5 from original data)
If we round to nearest 10's using even-multiple-of-10 roundup rule for 15, 25, 35 (i.e. 15 rounds to 20, 25 rounds to 20, 35 rounds to 40 45 rounds to 40 etc), then we get a set of data whose average is 55.06 (change of ~0.06 from the original data... much closer to original data).
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding of numbers
Assuming a perfect uniform distribution, and there is one instance of each number x.5000 to x+1.4999, then if you round up the 0.5000, then total roundoff error will be 0.5.
...
That leave x.5000 which has a roundoff of +0.5 and x+1.0000 which has a roundoff of 0. As a result, the sum of all the roundoffs between x.5000 to x+1.4999 is 0.5.
If the 0.5000 is rounded down, then the sum of all the roundoffs between x.5000 to x+1.4999 will be -0.5
By rounding the 0.5 to the nearest even number, half of the 0.5's are rounded up, and half are rounded down, thus cancelling out the positive and negative .5 roundoff errors.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: Rounding of numbers
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding of numbers
Truth be told, most of the time, my stuff has trouble with determining whether the third digit is even correct at all. And since none of my customers are financial, they expect to see the conventional rounding process.
Excel and Mathcad carry something like 15 digits of precision and how a number that can't be represented precisely in binary floating point is rounded in the 15th decimal place is of purely academic interest.
Unless you have some desire to truncate the precision that's carried in these programs, the issue of rounding never really enters into any final calculation, since the underlying calculations are done to the full precision of the program, regardless of what's displayed.
So, no process changes, but I'll certainly keep this discussion in mind the next time I have a run-in with my bank.
TTFN
RE: Rounding of numbers
If I can paraphrase, you mean you were wrong (when you said "rounding up does split the field in half"... "there is no bias"), but you no longer think the subject was important to begin with. Correct?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding of numbers
saludos.
a.
RE: Rounding of numbers
2.5 rounds to 2
3.5 rounds to 4 therefore, 3 gets shorted by one sample in an even distribution.
TTFN
RE: Rounding of numbers
Every year I have to present to management a rolling 5-year plan where line items are estimated in current dollars to the nearest $1,000 and totals per year are 2 - 3 million. The out-year costs are escalated which gives values having fractions of thousands. The entire spreadsheet is displayed rounded to thousands, with totals at the bottom of each year. Last year a manager pointed out that the totals at the bottom of one year are off by $1,000 compared to the actual totals. Obviously it was those hidden rounded fractions from escalation that got me. The resulting change in $1k insignificant to the big picture but is an unecessary diversion and could possibly undermine my credibility in the eyers of someone looking quickly at the printed spreadsheet. Next time I'm going to look at using precision as diplayed to avoid this.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding of numbers
In your particular example, neither normal nor even rounding would guarantee a clean sheet, since financial numbers of your kind are rarely evenly distributed.
TTFN
RE: Rounding of numbers
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding of numbers
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Rounding of numbers
RE: Rounding of numbers
0.798*e*sqrt(N)
But whilst this can be highly important for the money-men, in general for engineering-type problems I'm with IRstuff: discussions about the appropriate treatment for X.5000 in rounding is interesting in theory but irrelevant in practice.