Ask Your Question
0

calc funny result with cell formatted as 'fraction'

asked 2020-10-12 09:30:33 +0200

newbie-02 gravatar image

updated 2020-10-18 23:59:59 +0200

hi @all,

playing around with numbers and formatting i keyed in =3333333333/100 in a cell and formatted it as fraction with the format-string '?/???' (i wanted to display '3333333333/100' to come near to integer math),

displayed result: -961633963/100 ?!?!

bug? limitation? 32-bit math? or did i do something wrong?

behaviour applies to values which stay with the denominator 100, others which can be reduced to 50-th or 25-th display correct,

same behaviour for plenty other values, seems to start with 2147483648/100,

happened in 7.1 from 2020-10-04, 6.1.6.3 same behaviour ...

formatting to '?/100' fails for all values above 2147483647, formatting to '?/50' holds till double that value, i suspect fail results from enumerator being above 2147483647 ... 231-1 holds and 231 fails

[edit]

edited to provide some info for @Lupp, actually it is OT for this question, but where the discussion is going so well ...

question 1 is how and why do you or calc assume double floats below a certain threshold to be integers, neglecting that they may be rounded representations for fractions, and deny the same assumption for bigger values as it was taught in the cited text: 'So any mathematical operation that would have resulted in an odd number in this range will instead be rounded to one of the even numbers around it. But the result will still be an integer.' is there any 'external clue' for 'small' doubles that they are! integers and not rounded fractions which i'd miss?

my comment 'small hope for change in near future' references to @erAck's explanation that (assumed) integers are differently handeled for display than decimal fractions or 'big integers' which calc doesn't assume integer,

you requested a sample sheet - ok, - i can deliver - click to download / open - ,

i would like my favorite spreadsheet to deliver a little less nonsensical results than those marked yellow and red in the attached table ... meaningful intelligent rounding - yes, exaggerated falsifying rounding to hide errors which hopefully have been solved in the meantime - no, and even less so if it is only applied to successive values alternately,

i have marked the desired values in green next to it ...

P.S. please be indulgent if i got lost in details, it is about the basic concept ...

[/edit]

edit retag flag offensive close merge delete

Comments

Ad "question 1 is..."
I don't. You may interpret the behavior of Calc this way, but it will not be fruitful. It's the other way round: Double FP-numbers with an effective dyadic exponent >=53 are supposed to not be integer because the range being represented by the exactly same bit pattern contains more than one integer number, and there is no information available which one should be preferred. Concerning numbers with effective dyadic exponents 0<=e<=52 there is at least a realistic probability that they may be (in mind, not in sheet) be assured to be integer due to the ways they were calculated, and to the ideas behind the sheet formulas about which Calc can't know anything. In the context it may be useful to remember that the function INT() also returns a Double result in every case, and that it simply is the ...(more)

Lupp gravatar imageLupp ( 2020-10-19 02:37:08 +0200 )edit

(Hope to find time tomorrow to comment on your claim concerning "nonsensical results". Cannot be sure.)

Lupp gravatar imageLupp ( 2020-10-19 02:41:25 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2020-10-12 15:54:34 +0200

erAck gravatar image

Simply a bug. Please submit.

edit flag offensive delete link more

Comments

done, tdf#137453, would be nice if somebody rechecks on a lin system,

newbie-02 gravatar imagenewbie-02 ( 2020-10-13 15:06:14 +0200 )edit

Already done.

erAck gravatar imageerAck ( 2020-10-13 16:59:24 +0200 )edit

@erAck: looks better now, there are still limitations (now not in the value of the denominator but 'the value of the term' not exceeding 2^32-1 ?) but they are displayed properly as #FMT, thanks for fixing this bug,

newbie-02 gravatar imagenewbie-02 ( 2020-10-15 12:44:01 +0200 )edit
0

answered 2020-10-12 17:16:15 +0200

Zizi64 gravatar image

updated 2020-10-13 11:53:05 +0200

The format code

?/100

works for me fine up to 2^31-1 (= 2^30+2^29+...+2^1+2^0) value of the numerator. It means the upper limit of a positive 32 bit signed integer type numbers.

(The 31. bit is reserved for the sign.)

This is the "limit" for the Fractions (today).

edit flag offensive delete link more

Comments

Yes. The routine seems to use a 32-bit-integer for the numerator. Using a Double variable in this place should fix the problem to a certain degree. Extending the given example to 15 digits "3" would do, at least.

However, the routine needs to catch cases where the numerator cannot be assured to convert exactly to an integer the ordinary way. Above that limit the returned format should be "scientific".
If 64-bit-integers are available, they can extend the range of cases where switching to a fallback format is avoidable.

BTW: The largest integer (whole number) amount that can be represented by IEEE 754 Double is 9007199254740991 (2^53-1). The positive number with this amount has the bit pattern 0100001100111111111111111111111111111111111111111111111111111111 (most significant bit first).

Lupp gravatar imageLupp ( 2020-10-12 17:27:48 +0200 )edit

@Lupp: sorry for objecting ... you are the teacher, i'm the pupil, but ...
(it's not important for the original question, just to clear and recheck my thoughts)
253 is! an integer and can! exactly be represented in decimal and 64-bit floats, similar holds for plenty values above,
a problem is that above 253 only every second integer can be exactly build in 'doubles', with this 'granularity' doubling with the value,
numbers up to ~1,9999999999999998889775 * 21023, are covered by float doubles, even in calc, that might be a decimal fraction, (didnt' check as 308 digits would take too long), but other values in that range should be (mathematical) integers, at least 1 * 21023,
a problem in calc is that it switches to scientific representation somewhere and rounding somewhere else (last even different for integers and fractions),
the 'datatype'! 'integer' is different and is subject to ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-10-13 11:46:53 +0200 )edit

The display of IEEE 754 double integer values (up to +-(2^(53-1))) is not rounded. Stop spreading this nonsense please.

erAck gravatar imageerAck ( 2020-10-13 16:54:58 +0200 )edit

Double type has no attribute telling whether or not the represented number should be regarded integer. It simply has a 52-bit (+1 implicit) mantissa, and must always be suspected to be the rounded result of some calculation (if not a user can assure otherwise). However, if the semantic dyadic exponent (order of magnitude) is >52, the represented number cannot be an exact integer if not additional/absent dyadic digits (-1st, -2nd, -3rd... to say behind the zeroth) are in some way assured to be 0.
The fact can be exemplified this way: If you try to increment (2^53 - 1) by 1, the incrementation of the mantissa will fail. All ones is allready the maximum. The exponent must be increased ...
You may design a FPU dedicating one of the 64 (or whatever) available bits to the information meaning "This number is assured to be integer". FLOPs would then need to ...(more)

Lupp gravatar imageLupp ( 2020-10-13 17:29:56 +0200 )edit

The double to string conversion detects representable integer values within that (2^53 - 1) range.

(and +-(2^(53-1)) above was a typo, it's +-(2^53-1) of course).

erAck gravatar imageerAck ( 2020-10-13 17:40:26 +0200 )edit

@erAck: 'The display of IEEE 754 double integer values (up to +-(2^(53-1))) is not rounded.'
sorry for misunderstanding, i wrote that the display of 'integers' is rounded above some threshold in calc?, not restricted to IEEE and not restricted to the range +/-(253-1),
ot for this question:
afaik rounding starts at +/-253 and is 'rounding to 15 significant digits' which replaces the 'step-2' accuracy / granularity possible in this area acc. IEEE with a 'step-10' and above 10.000.000.000.000.000 with a 'step-100' granularity,
in my opinion unnecessary and wrong as 'step-2' is possible up to 18.014.398.509.481.984,
just think it further, values above 1.000.000.000.000.000.000 have a display accuracy of ~10.000 in calc while calculating by double floats delivers a step-128 precision, that's a factor of ~78 of the realized deviation in ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-10-14 11:43:21 +0200 )edit

The IEEE 754 double precision format is a Floating point number type. Of coures it can handle the integer values too, but that is not a"pure" Signed Integer type.

https://en.wikipedia.org/wiki/Double-...

The Integer types can handle ONLY the integer values. The Signed ones can handle the negative values, but the Unsigned ones can not handle the negative values. An overflow of a Signed integer (can) results a negative number, because the most significant bit (the sign bit) will be set.

https://en.wikipedia.org/wiki/Integer...

Zizi64 gravatar imageZizi64 ( 2020-10-14 12:10:44 +0200 )edit

@Lupp and @erAck: is this correct:
a double float represents a 'range' of values, 'from the bits' one cannot decide which value it is,
(1,0 * 252, can be exact 4.503.599.627.370.496, but as well ~4.503.599.627.370.495,76 or ~4.503.599.627.370.496,49),
in the range +/-253-1, if!!! you have the 'external clue' or expectation that it should be an integer, there is only one value,
outside that range (abs value > 253) the range a double float represents contains two or more integers, 'from the bits' one cannot decide which is the correct one, and thus the info 'should be integer' is not enough to have a distinct value, so integer calculations outside this range are 'fuzzy' when done with doubles,
insofar 'quads' would give an expanded range where int-calc is 'sure', and better ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-10-15 12:34:54 +0200 )edit

Binary floating point is always limited in representing decimal values, no matter how far you push the limit by using double double. Play with http://weitz.de/ieee/ and to understand why a double can't represent an exact integer >=2^53 read https://blog.reverberate.org/2014/09/...

erAck gravatar imageerAck ( 2020-10-16 15:38:00 +0200 )edit

@erAck: yeah, from info like that i expected: "No — again for integers the news is much less dire. Between 2^24 and 2^25 a float can exactly represent half of the integers: specifically the even integers. So any mathematical operation that would have resulted in an odd number in this range will instead be rounded to one of the even numbers around it. But the result will still be an integer."
expected similar for doubles with 'range-break' at 253, in detail 2<sup>53</sup>)+2 being an integer, and calculation and display! precision in that range being 2
in calc i'm confronted with:
a numercal display string of 9.007.199.254.740.990 instead of ~994,
9.007.199.254.741.000 for 2<sup>53</sup>)+4 instead of ~996,
@Lupp saying they are not! integers,
a rounding 'towards zero',
let me say i'm 'irritated' ...
i know ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-10-17 12:58:36 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-12 09:30:33 +0200

Seen: 94 times

Last updated: Oct 18 '20