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?

 

[electricpete]

Probably the better approach would be keep precision as displayed "off", but make my total a sum of the rounded line items rather than sum of the actual line items. That way other caluclations in the spreadsheet won't be affected.

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

 

[BigH]

electicpete - might I suggest that you add a footnote to your spreadsheet which indicates that the actual values might not match the presented values by XXX due to the actual value roundoffs - as per $1000. This way you cover your credibility.

 

[Denial]

Taking ElectricPete's budgeting "anomaly" a bit further, it is to be expected that the rounded total of a set of numbers will not usually equal the total of the rounded numbers. You can apply statistical mechanics to the problem, by treating it as a "one-dimensional random walk", to show that if the average magnitude of the rounding error is e ($250 in ElectricPete's case), then the expected value of the magnitude of the discrepancy when summing N values is
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.