What Does ½ Really Round Off To?

[LSLGuy]

@Dr.Strangelove

@Chronos

The actual line of scrimmage is real-valued

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

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.

@Quercus

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)

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.

@74westy

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!

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.

[Atamasama]

@LSLGuy

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 generally go to Technet for that kind of info.

[74westy]

@Atamasama

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.

[LSLGuy]

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:

Workflow

[LSLGuy]

@74westy

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.

You already posted the link to it:

@74westy

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).

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

[Chronos]

@k9bfriender

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.

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.

[Thudlow_Boink]

@Chronos

Yup, and does -1.5 round “up” to -2, or “up” to -1?

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

@tofor

These all round to 2:
1.59999999
1.50000001
1.5

It’s easy to see that rounding up is the right answer for the first two, and for the third we are just being consistent.

[Melbourne]

@YamatoTwinkie

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().

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.

[DMC]

@Melbourne

BA 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.

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.

[Melbourne]

@DMC

VBA actually does have a ROUND function

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

[LSLGuy]

@DMC

As you noted it uses banker’s rounding, just like the rest of .NET.

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

Math.Round Method (System)

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

MidpointRounding Enum (System)

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