MATH: FLOOR, CEILING, MROUND functions: fails with extreme values.

Thanks to Glenn Kennedy,

> It's probably down to floating point arithmetic.. but it's just a guess.

_assume_ in old versions it's coded as simple 'divide - truncate - multiply' what can't work
in limited datatypes for big spread number - significance.

> No. Ignore that. All 3 work perfectly for me, using O365.

Thanks, that was the info I wanted, first to see that Microsoft is making progress, and then
to avoid embarrassing myself elsewhere in my work with outdated info.

sorry, I made a mistake, I wanted to ask if

floor( -9E+307, -0.1 ),
floor( -9E+307, 0.1 ), and
floor( 9E+307, 0.1 ) have meaningful numerical results or -> #NUM! ?

hello all,

sorry I had a fail / typo when first asking this, thus again:

I have some questions about handling of values and simple formulas in Excel.
I see some differences to what's defined / announced, alas my version of Excel is rather old,
thus also info if the issues are improved in the meantime are welcome.

FLOOR function: Microsoft announces on the site defining the function:
support microsoft com en-us office floor-function
'Rounds number down, toward zero, to the nearest multiple of significance.'

but plenty of tasks with extreme values fail, e.g.:
floor( -9E+307, -0.1 ) -> #NUM!,
floor( -9E+307, 0.1 ) -> #NUM!,
floor( 9E+307, 0.1 ) -> #NUM!,

I consider that a break in math logic and assume some users / calculations might become
trapped / harmed.

Any clue behind that?

Pls. no answers like to avoid such calculations, I can once I know about and am 'steady aware',
other users or calculations in complex sheets / formulas can't.
I'm just curious why it is as it is, and if improved in newer versions.

TIA for any help.

hello at AliGW,

would have been better to delete this thread and let the other one open, this has a typo and sends users
to nirvana.

Am I allowed to ask similar questions about other functions? There are some more with questionable results,
I thought to handle separately. ok?

ok,

floor( -9E+307, -0.1 ),
floor( -9E+307, 0.1 ), and
floor( 9E+307, 0.1 ) have meaningful numerical results or -> #NUM! ?

note 9E !!!PLUS!!! 307 ...

at MrShorty,

yes, I do, and also assume that the root cause,

If your description is still valid you are also on an old ver. of Excel?

think to gain math / definition compatibility such could be substituted by a simple decision
instead of calculating to not representable numbers, but didn't know if modern Excel would
apply such or perform the hopeless calculation.

Assume similarly you still have:

ceiling( -9E+307, -0.1 ),
ceiling( -9E+307, 0.1 ),
ceiling( 9E+307, 0.1 ), and

mround( -9E+307, -0.1 ),
mround( 9E+307, 0.1 ) -> #NUM! ?

And am curious if it's still the same on more recent versions.

> felt the need to say this

The more nonsense I find in binary floating point arithmetic and its applications, the more humor aside I need to keep my spirits up.

IMHO we and Excel try to do some kind of math, and a relevant part of math is 'closedness', to produce either the defined results,
or a meaningful ERR-notice, NOT! wrong results.

In hope MS does care for forums as feedback, and as question at others to check with more recent versions:

'=trunc(2.25E-308, 32768 )' -> 0 ???

'=roundup(2.25E-308, 32768 )' -> 0 ???

'=round(2.5, 2147483648 )' -> 0 ???

To understand: it's not that I want, need or plan to calculate such tasks, but I'm looking
where bin-FP errors evolve from, see risks in complex formulae where tasks are calculated
which no human is controlling, and have trouble with other systems who claim to need to
continue such nonsense reg. 'Excel compatibility'.

TIA for any recheck :-)

at MrShorty, thank you, yes, know these articles, think contributed to those on wikipedia, but my question here isn't what
IEEE can do, but what a programmer can / should do with it. IEEE isn't defining a new math, it just has shortcomings, and
it is the responsibility of the programmer to choose e.g. rounding modes or 'well conditioned formulas' to achieve his, his
customers or our goals.

In that sense I think that while we face some lack of precision in 'last bits', it is! possible to calculate results
( nearly ) matching Excels definitions also for most extreme cases, and I'm curious if
- it's still not done in recent versions?
- there is any other 'why' that I'm not aware of?
- I'm the only idiot on this planet seeing a break in consistency and risks for unwary users ...

To cite from 'Goldberg' he condems '... then the result of further operations will depend on the hidden digits and
appear unpredictable to the user.' as bad behavior which I second to. And returning results not matching the
own definition is - IMHO - also unpredictable and user trapping.

To - try to - put in in simplier words, IMHO it's different things:
- to have small imprecision in results due to imprecise binary deputies or rounding,
- to have questionable definitions for functions ( that will be the next question ), or
- not fulfilling / matching the definitions and returning mathematically wrong results
to 'simple minded users'.