What Does ½ Really Round Off To?

I have to say over the years I’ve seen the refs spot balls in places that can only be described as surreal compared to the results of the play I just watched.

Given MSFT’s dedication to avoiding breaking changes, I’d bet that’s not the case. IMO their sin is mostly that they’re trying to write documentation that won’t overwhelm Sally from data entry or the typical PHB with details they cannot comprehend. Each page really needs a nerd-mode switch that will expose the other 5 pages of nitty gritty for those who care.

I just love it when the examples are ambiguous! Morons.

My favorite example of that is the various formatting settings in Windows and Excel for dates and times where the example uses a two digit month or 2 digit hour so you can’t tell how leading zeros will be handled, or shows a time value before 1200 so you can’t tell how afternoon time will be handled. Will 1pm be “13:00” or “1:00” or “01:00” with or without a trailing “pm” and with what spacing? No way to know since the example value they picked was 10:15.

I generally go to Technet for that kind of info.

I couldn’t easily find the ROUND function in excel so here’s the documentation for ROUND in t-sql. Please read it and tell me what method it uses.

ETA: @Atamasama 2 posts above …

My cite also leads to MSFT’s official documentation. FYI “Technet” was the name of the IT administrator part of Microsoft’s documentation site back 20ish years ago. Hasn’t been called that for years. Likewise “MSDN” was the name for the dev documentation part.

The problem is their documentation for Office is aimed way too much at the lowest common denominator end user, not the “power user”, and certainly not the developer. Even though they’re still pushing development of Office-based apps.

Even the C# or .Net Framework docs can be maddeningly incomplete in spots. Some of that may be deliberate so as not to create contractual behavior, but again given their admirable dedication to backwards compatibility, everything about an API becomes contractual very quickly after it’s released into the wild. Even the bugs.

See Hyrum’s Law for more which includes a reference to this wise commentary:

You already posted the link to it:

That IS the official site/cite for MFST Office documentation.

Yup, and does -1.5 round “up” to -2, or “up” to -1? Our grade school teachers probably never specified. Those probably represent two of those 11 different rounding functions. And yes, I would expect that the generic “round” function probably is one of those two.

-1.500001 is closer to -2 than to -1. So if you follow the “consistency” rule, it would round to -2.

Or use VBA. VBA doesn’t have a ‘round’ function as such, but functions such as CINT use the rounding function that is part of the Windows API. And (since sometime last century), it’s bankers rounding.

I’m sure that many people regret that Excel doesn’t do bankers rounding, but many many more just want Excel to give exactly the same answer it always has, and where that means Lotus 123 compatibility, it is what it is.

VBA actually does have a ROUND function. As you noted it uses banker’s rounding, just like the rest of .NET.

I created a simple VBA function that performed a ROUND using the VBA version and returned it. When called in Excel, 1.15 returns 1.2, 1.25 also returns 1.2.

Showing my age. Most of my VBA was last century.

Bankers is the default, but beyond the many numeric type conversion functions and interfaces there’s

Which takes an optional parameter to control the kind of rounding:

I expect you know all this, but I hunted up the cites for others who might be interested.

I always round x.5 up to the next number. For example, 1.0-1.49 would round down to 1 (yes, 1.0 is already 1) and 1.5-1.99 would round up to 2. That’s how I was taught.