Calc funny result with cell formatted as 'fraction'

English
, , , , ,
#1

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]

fraction formatting result different to ms excel
#2

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 identity for FP-numbers >= 2^53. If you suppose the result to be a “mathematical” integer, you are wrong. For the FPU it’s a range.

#3

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

#4

Simply a bug. Please submit.

#5

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

#6

Already done.

#7

@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,

#8

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

#9

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

#10

@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 tighter restrictions,
ok?

#11

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

#12

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 perform an additional,calculation like
i {+, -; *} i --> i, … I think it wouldn’t pay.

#13

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

#14

@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 display against technically neccessary

#15

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.

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.

#16

@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 precision for fractions, but the general problems of fp-math would stay

#17

Binary floating point is always limited in representing decimal values, no matter how far you push the limit by using double double. Play with IEEE 754 Calculator and to understand why a double can’t represent an exact integer >=2^53 read Floating Point Demystified, Part 1

#18

@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 and understand! ‘somebody at some time implemented rounding to 15 decimal digits’ as a fact, but not who and why … possible to change?

#19

Quoting @newbie-02: “Between 2^24 and 2^25 a float can exactly represent half of the integers: specifically the even integers.”
What did lead you to this absurd claim?

Quoting @newbie-02: "@Lupp saying they are not! integers, "
I couldn’t get from the context what you meant by this statement.

#20

The quoted text is from the article I linked. With single (float) between 2^24 and 2^25 only even integers are representable, as for double they are between 2^53 and 2^54.

The problem in our output is that for the representable integer range with precision 1 (up to 2^53-1) a different exact conversion to string is used, larger (absolute) values are fed through the general double floating point to string conversion, which has the rounding to 15 significant digits so displaying it forcedly with a 0 number format yields unexpected results.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.