Ask Your Question
0

series of values not monotone?

asked 2020-09-26 19:50:39 +0100

newbie-02 gravatar image

hi everyone,

some series of numbers look somewhat odd to me, e.g.

key 2.251.799.813.685.244 in a cell, and
'that cell' +0,25 in the cell below, you'll get 2.251.799.813.685.24 0, (sorry for the extra spaces, they were needed for the formatting),
copy the second cell down, the series will be:

2.251.799.813.685.244
2.251.799.813.685.24 0
2.251.799.813.685.24 0
2.251.799.813.685.24 0
2.251.799.813.685.245
2.251.799.813.685.24 0
2.251.799.813.685.2 50
2.251.799.813.685.2 50
2.251.799.813.685.246
2.251.799.813.685.2 50
2.251.799.813.685.2 50
2.251.799.813.685.2 50
2.251.799.813.685.247
2.251.799.813.685.2 50
2.251.799.813.685.2 50
2.251.799.813.685.2 50
...

while Weitz IEEE 754 calculator says that:

2251799813685244.0
2251799813685244.3
2251799813685244.5
2251799813685244.8
2251799813685245.0
2251799813685245.3
2251799813685245.5
2251799813685245.8
2251799813685246.0
2251799813685246.3
2251799813685246.5
2251799813685246.8
2251799813685247.0
2251799813685247.3
2251799813685247.5
2251799813685247.8

is correct,

every fourth number x, (in 'full bold'), is a 'pure integer', while the three inbetween are fractions x,25, x,50, x,75 to which obviously some rounding is applied,

but that rounding make no sense to me ... whatever it is good for, it breaks math logic!

i suspect that numbers with fractional part are handeled differently to those without?

ex$el in contrast - checked ver. 2010 win7x64 - changes the (display of) the first number to ~40, and produces a less accurate but 'homogeneous' series of 21 times ~40, and then continues with ~50,

has anybody any clue what went wrong?

reg.

b.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-09-26 21:27:48 +0100

erAck gravatar image

Display strings for non-integer values are rounded to at most 15 significant digits whereas integers, as longs as they can be represented in an IEEE 754 double, are displayed in full precision. This is why you see 2251799813685244 for 2251799813685244.0 but 2251799813685240 for 2251799813685244.3 (or .25 or whatever) if you force a fixed number format like 0 or 0.0, the General format displays 2.25179981368524E+015

edit flag offensive delete link more

Comments

hello @erAck,
thanks again for clarification,
but the combination of 'simple user' and 'calc rounding strategies' leads to 'funny sequences' and 'disturbed users', see comment C#21 there: tdf#129606
do you think that there is room for improvement?

newbie-02 gravatar imagenewbie-02 ( 2020-09-27 11:45:14 +0100 )edit

Difficult. There's no overall 16 decimal digits display precision, you will get wrong display values somewhere anyway. There might be some improvement possible for corner cases like this one here if someone really wants to dive into it without breaking things. The best would be to not round at all, also not during calculations and leave all rounding up to the user, and display all "wrong" (by limitation of IEEE 754) values as is.. that again is not expected by the user, but would be most correct. It's a completely different strategy though.

erAck gravatar imageerAck ( 2020-09-27 15:12:51 +0100 )edit

someone must have introduced rounding to 15 digits at some point, and will have seen the sense in it,
i would be curious if this sense is documented somewhere, and if it was a general consideration or a special case he wanted to solve,
i assume it was an attempt to suppress artifacts in the last digits resulting from FP calculations,
but to apply such rounding across the board creates other problems that are more difficult to understand,
when rounding, it should make sense, on the one hand you should round according to mathematical logic, 0.5 away from zero, on the other hand you should either 'round' integers as well, or floats keep 16 digits, otherwise you get - not only in this corner case - 'funky' number series ...
'smart rounding' acc. to the precision of input values and math logic could probably do better, but requires more effort ... :-(
simple question ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-09-28 10:49:26 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-26 19:50:39 +0100

Seen: 35 times

Last updated: Sep 26 '20