# What Does ½ Really Round Off To?

That’s not true. The officials place the ball where they believe it was downed. They never move it up or back to the nearest yard line. If the ball was downed outside of the hash marks they move it in to where the closest hash marks are, but not necessarily directly on a yard line.

Just for the heck of it, I used Excel to round a series of numbers that ended with .5 to an integer. They all (from 0.5 to 10.5) rounded up.

Note that the current version of Excel has 11 different rounding functions for the many various conventions used. There’s ROUND, ROUNDDOWN, ROUNDUP, MROUND, CEILING, CEILING.MATH, EVEN, FLOOR, FLOOR.MATH, INT, and ODD. Each of which does it a different way for a different purpose.

And if you experimented by just altering the cells’ formatting to display fewer (or no) decimal places, that’s a 12th sort of quasi-rounding that doesn’t affect the actual value used in calculations, just how it looks on the screen / paper.

See this for more on rounding in Excel:

The highlight text says Excel 2010, but the info is actually valid from Excel 2007 through current.

Which one of those does unbiased rounding? As I suspected, there’s no mention in the link provided other than to say that ROUND() rounds up for .5 which is more information than you get from microsoft’s ROUND() page.

And also “what are you rounding”. Modern computers commonly store fractions as binary numbers, internally they don’t have ‘numbers that end in 5’ at all. Re-imagining the number as a decimal fraction, then rounding either up or down, is something people do*, but it’s not something that has any sensible numeric explanation, because it involves rounding twice. The ‘smart’ way is to do only binary rounding as necessary, then round the final answer as part of the operation of converting from binary to decimal – an operation similar to decimal rounding, but not involving ‘5’.

* There are other reasons for doing intermediate decimal rounding, but they have more to do with people than with numbers.

I also didn’t learn about rounding n+0.5 to the nearest even number until college physics, when error analysis and propagation was discussed. The topic was further explored in my surveying class. I had never given much though to the rule of always rounding n+0.5 up to the nearest whole number, but after learning the even number rule I wondered how I never noticed the bias issue.

Someone mentioned how Excel will visibly truncate numbers to fit into cells while still retaining the “real” value of those numbers for computation purposes. Most calculators do this too. The display of many calculators limit how many digits can be shown, but there are often one or more “guard digits” which are stored in memory but not displayed. The calculator might display a computed result to 12 digits, but actually stores that result to 13 (or more) digits. You can read more about guard digits here.

Thank you! I thought I was having deja vu all over again. It hasn’t even been a year.

And I too had never heard of Banker’s Rounding. It does at least make some sense.

I misspoke-- The actual line of scrimmage is real-valued, but the reported value will be an integer, and never 0. If the line of scrimmage is 181 inches from the goal line, that’ll show up on the scoreboard as “First and goal, at the 5 yard line”, and if it’s 1 inch from the goal line, it’ll be “First and goal, at the 1”.

So you say. I’m less certain that the universe operates on real numbers. It might use hyperreals, or even the surreal numbers.

I don’t think any of excel’s native rounding functions does “round half to even” which is the typical method to eliminate positive/negative bias and bias away from zero.

To get this in excel, you’re probably going to have to use a nested =IF() logic function to check for positive/negative numbers cleanly divisible by 0.5, then round only those to even using MROUND(), then round the rest using ROUND().

This should work in Excel, where A1 is the cell value you want to round:
=IF(MOD(A1,1)=0.5,MROUND(A1,(SIGN(A1)*2)),ROUND(A1,0))

I looked up Microsoft’s online documentation for its ROUND function, and, unless I missed it, it didn’t say which method it used to round.

It’s quite possible that some more technical documentation somewhere does specify how it does its rounding. But if not, that could mean that it’s unpsecified, and that different implementations of Excel could do it different ways. Which could cause interesting side effects, I suppose.

They have 11 different rounding functions, and their documentation doesn’t specify how the most basic one of the lot works? That’s absolutely insane.

Absolutely insane and absolutely typical. I work with microsoft products every day.

You haven’t dealt with, well, any commercial software, much, have you? Or maybe I should say I admire your ability to preserve your sweet childlike innocence, even in the face of experience.

Anyway, in Excel it appears Round (12.5,0) is 13 [the zero as the second argument is saying round to zero digits after the decimal]. And Round (13.5,0) gives 14. So it appears Excel Round() always rounds 0.5 up. But of course, that may be different in a different version of Excel (and I have a cynical suspicion is is different, which is the reason the documentation doesn’t commit to anything. But that may be tooo cynical)

That’s where the round to even rule would create more consistency, anyway.

Indeed, the software documentation I’m more familiar with is for software created by amateurs. Who, apparently, do a much more professional job than the actual professionals.

What I would expect is a plethora of specialized rounding functions, all with specific names like `round_m` or whatever, and each with its own specific definition. And then, the entry for the function named just `round` would just say that it’s a synonym for one of the others, whichever one the developers thought was the most generally-useful. That way, a user who really does need to be particular about exactly how their rounding works can look up exactly the one they want (even if the one they want happens to be the default one), and be sure of what they’re getting, and the casual user who doesn’t care can just say `round` and not worry about it.

At worst, if it did change between versions (sloppy, but sometimes the least-bad option), I would expect something like “In versions 7.1 through 9.3, `round` is a synonym for `round_m`, while in versions 9.4 and above, it is a synonym for `round_b`. If the exact behavior is important for your application, use of `round_m` or `round_b` is recommended.”

Full disclosure: @LSLGuy’s link on the 11 rounding functions does say:

The ROUND function rounds a number containing a fraction as follows: If the fractional part is 0.5 or greater, the number is rounded up. If the fractional part is less than 0.5, the number is rounded down.

That information does not appear on the specific page for the ROUND() function (where you might look if you’re wondering how that function works). It does have a few examples and, mischievously, some of them show a number on the midpoint being rounded and in every such example, round .5 up and round to even give the same answer. It’s as if they’re taunting us!

Amateurs are creating something for the community. They want others to use and to build on what they have created.

Professionals are doing the least they can get away with while drawing a paycheck. Poor documentation is a means of job security.

I would generally assume that the generic round function would be the one that most of us learned in grade-school, and many never learned anything else, that a 5 and up gets rounded up. Any different rounding function should be specified.

Thanks, I missed that.

Yes, I did notice that!