4 ms·

The really surprising* thing is that Google Sheets uses floats for everything. Back in the day, I was using Sheets to do some statistics about (IIRC) kernel ASL

by t8sr 4y ago

The really surprising* thing is that Google Sheets uses floats for everything. Back in the day, I was using Sheets to do some statistics about (IIRC) kernel ASLR on macOS, and I was surprised to see kernel pointers ending in impossible digits. Of course only after I'd wasted 2 hours on it. Boy, did I file a pissy bug with the Sheets team that day, and then requested an Excel install I never let go of since that day. * I guess it's maybe not surprising to js developers, but don't most modern browsers have integers by now?

altruios 4y ago
besides bigInt... What numbers in javascript are not actually doubles (what js uses under the hood for every number)? You can convert them from doubles to 32-ints apparently by some bitwise hacks like (I'm pretty sure they still are 'doubles' though - and it just does some rounding tricks) ``` |0 //signed >>>0 //unsigned ``` So any webapp (google sheets) will likely have problems related to floating point math.
- ElectricalUnion 4y ago
  > You can convert them from doubles to 32-ints You can use the native BigInt arbitrary-precision integers and ignore all "fits under this arbitrary limited bit slice" problems. You can use doubles as a integer directly, in a safe way, until you touch the 53 bits barrier (Number.MIN_SAFE_INTEGER === -9007199254740991 or Number.MAX_SAFE_INTEGER === 9007199254740991). None of those really are (modern) webapp issues.
jkaptur 4y ago
How do Sheets and Excel differ in this regard? How did using floats cause some number to be odd?
- jdmichal 4y ago
  GP didn't say odd, or at least not numerically odd, but rather "ending in impossible digits". Floats have varying precision, not uniform. The closer the number is to zero, the more precision it has. There's a point where the precision is so low that it only covers whole numbers, and then a point after that where the precision is less than whole numbers. Given the context of pointers, it's quite possible that they were large enough to reach that less-than-whole-number-precision range. EDIT: For 32-bit floats, that's apparently any number above 16,777,216. Which seems surprisingly small to me. For 64-bit doubles that goes to 9,007,199,254,740,992. https://blog.demofox.org/2017/11/21/floating-point-precision https://blog.demofox.org/2017/11/21/floating-point-precision
  - jkaptur 4y ago
    I think the comment may have been edited ;) Anyway, yes, that's all true. Excel, Sheets, JavaScript numbers, Java doubles, Python floats, and so on all work this way. That's why I asked how switching spreadsheet implementations solved the problem.
    - jdmichal 4y ago
      Ah, sorry for that I must have seen the updated one. I agree with you that switching programs that are all using the IEEE floating-point formats with the same underlying hardware implementations should result in the same answers.
ElectricalUnion 4y ago
> Google Sheets uses floats for everything (...) and then requested an Excel install But Excel also uses floats (as in, Single-precision floating-point format, aka float32), because of "compatibility with Lotus 1-2-3". https://softwarerecs.stackexchange.com/questions/53292/any-spreadsheet-provide-more-then-double-precision https://softwarerecs.stackexchange.com/questions/53292/any-s... https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result https://learn.microsoft.com/en-us/office/troubleshoot/excel/...
- mananaysiempre 4y ago
  Excel uses floats in its own ways that even experts on floating-point arithmetic find inscrutable[1]. [1] https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf, §2
- t8sr 4y ago
  Ah, but it used ints (or bignum) to represent my pointer values, instead of silently losing precision. This is an anecdote, maybe there are other gotchas I am blissfully ignorant of. (Also, this was 8 years ago.)

by t8sr 4y ago

altruios 4y ago

besides bigInt... What numbers in javascript are not actually doubles (what js uses under the hood for every number)? You can convert them from doubles to 32-ints apparently by some bitwise hacks like (I'm pretty sure they still are 'doubles' though - and it just does some rounding tricks) ``` |0 //signed >>>0 //unsigned ``` So any webapp (google sheets) will likely have problems related to floating point math.

ElectricalUnion 4y ago
> You can convert them from doubles to 32-ints You can use the native BigInt arbitrary-precision integers and ignore all "fits under this arbitrary limited bit slice" problems. You can use doubles as a integer directly, in a safe way, until you touch the 53 bits barrier (Number.MIN_SAFE_INTEGER === -9007199254740991 or Number.MAX_SAFE_INTEGER === 9007199254740991). None of those really are (modern) webapp issues.

jkaptur 4y ago

How do Sheets and Excel differ in this regard? How did using floats cause some number to be odd?

jdmichal 4y ago
GP didn't say odd, or at least not numerically odd, but rather "ending in impossible digits". Floats have varying precision, not uniform. The closer the number is to zero, the more precision it has. There's a point where the precision is so low that it only covers whole numbers, and then a point after that where the precision is less than whole numbers. Given the context of pointers, it's quite possible that they were large enough to reach that less-than-whole-number-precision range. EDIT: For 32-bit floats, that's apparently any number above 16,777,216. Which seems surprisingly small to me. For 64-bit doubles that goes to 9,007,199,254,740,992. https://blog.demofox.org/2017/11/21/floating-point-precision https://blog.demofox.org/2017/11/21/floating-point-precision
- jkaptur 4y ago
  I think the comment may have been edited ;) Anyway, yes, that's all true. Excel, Sheets, JavaScript numbers, Java doubles, Python floats, and so on all work this way. That's why I asked how switching spreadsheet implementations solved the problem.
  - jdmichal 4y ago
    Ah, sorry for that I must have seen the updated one. I agree with you that switching programs that are all using the IEEE floating-point formats with the same underlying hardware implementations should result in the same answers.

ElectricalUnion 4y ago

> Google Sheets uses floats for everything (...) and then requested an Excel install But Excel also uses floats (as in, Single-precision floating-point format, aka float32), because of "compatibility with Lotus 1-2-3". https://softwarerecs.stackexchange.com/questions/53292/any-spreadsheet-provide-more-then-double-precision https://softwarerecs.stackexchange.com/questions/53292/any-s... https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result https://learn.microsoft.com/en-us/office/troubleshoot/excel/...

mananaysiempre 4y ago
Excel uses floats in its own ways that even experts on floating-point arithmetic find inscrutable[1]. [1] https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf, §2
t8sr 4y ago
Ah, but it used ints (or bignum) to represent my pointer values, instead of silently losing precision. This is an anecdote, maybe there are other gotchas I am blissfully ignorant of. (Also, this was 8 years ago.)