Rounding of numbers

[BigH]

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?

 

[IRstuff]

That's been convention since I was in grade school. 5 and above rounds up.

TTFN

 

[CajunCenturion]

In accounting circles, when dealing with .50, rounding to the nearest even number tends to minimize rounding error. The theory is that half the nearest even number will require rounding up, and half the time, the nearest even number will require rounding down. Over time, the rounding error is minimized.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[johnwm]

Traditional math rounding should round 5 up. Statistical rounding should round 5 to even. In Excel you also have the Round function, the RoundUp function and the Odd and Even functions. Excel Help will distinguish between them.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[electricpete]

Interesting.

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.

 

[IRstuff]

Excel, of course, still has a "precision as displayed" mode that permanently removes the extraneous digits as they occur.

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

 

[CajunCenturion]

Rounding at 5 does not split the field in half because there is no rounding at 0. Rounding only occurs at 0.001 to 4.999 and 5.000 to 9.9999. If you always round the 5's up, then you would be rounding down 4,999 values (0.001 to 4.999) and rouding up 5,000 values (5.000 to 9.999). In the case of a bank paying interest on savings accounts, the bank would be paying out more interest that it should because of the higher number of values which would be rounded up.

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

 

[CajunCenturion]

Oops. Got some up's and down's reversed.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[IRstuff]

Interesting rationale, but:

> 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

 

[electricpete]

Some explanation why consistently rounding 5 up (rather than rounding to nearest even number) can cause problems.

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.

 

[electricpete]

Correction:

http://home.houston.rr.com/electricpete/Round5rev1.xls

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.

 

[CajunCenturion]

==>If the x.500 to x+1.499 values are evenly distributed

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.
[li]x.5001 has a roundoff of +0.4999 and 1.4999 has a roundoff of -0.4999 and these will cancel out.[/li]
[li]x.5002 has a roundoff of +0.4998 and 1.4998 has a roundoff of -0.4998 and again, they cancel out.[/li]
[li]x.5003 has a roundoff of +0.4997 and 1.4997 has a roundoff of -0.4997 and again, they cancel out.[/li]
...
[li]x.9999 has a roundoff of +0.0001 and 1.0001 has a roundoff of -0.0001 and again, they cancel out.[/li]

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

 

[electricpete]

So IRStuff, have you changed your mind yet?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

Unless you're a banker and are paying interest and are keeping track of millicents, it's not an issue in most engineering calculations whether the average is off by 1/2 of the rounded digit.

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

 

[electricpete]

I have a hard time interpretting how your most recent comments are relevant to resolving the disagreement of your earlier comments.

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.

 

[abeltio]

electricpete... give the man a break... happy new year P


saludos.
a.

 

[IRstuff]

As I stated, I agreed that the average is more accurate with even rounding. However, there is still a bias in that the distribution is no longer evenly allocated to the rounded bins.

2.5 rounds to 2
3.5 rounds to 4 therefore, 3 gets shorted by one sample in an even distribution.



TTFN

 

[electricpete]

I can think of one instance where precision-as-displayed would be useful to me:

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.

 

[IRstuff]

You should use precision as displayed only as a display mechanism, otherwise, you'd lose the remaining digits in any subsequent calculation.

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

 

[electricpete]

In my particular example, I will consider using precision as displayed to ensure that the total below each column matches the total of the displayed numbers. It is obvious that this comes at the expense of a small loss of accuracy As I said a thousand [or two] is insignficant to the big picture. A tradeoff in presentation vs substance... both are important.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.