5 ms·

I guess I'm confused what alternative you want, to display long numbers? Even more so than a sheet of paper, an excel doc has tiny windows to display a huge pot

by mNovak 4y ago

I guess I'm confused what alternative you want, to display long numbers? Even more so than a sheet of paper, an excel doc has tiny windows to display a huge potential range of values, and something like 0.00000567 simply might not fit. Seems like if it bugs you, just ctrl+a then set the number format, same way you might set the font and size at the start of a word doc.

justsomehnguy 4y ago
Yes, display long numbers. Exactly why this is not a sheet of paper - I can resize the columns all I need. Just tested in LibreOffice calc: 30937930503010769238 is mangled to scientific notation: 3,09379305030108E+19 . If I change the format to anything (but it's already a generic number) than it becomes 30 937 930 503 010 800 000.00 or something. And if I change the format of the cell to the text it becomes... 3,09379305030108E+19 again. And yes it's a number, though it doesn't get used as such in my case. If it was really used for anything of value - I would be really pissed for the 30k inaccuracy.
- zamadatix 4y ago
  You may be (genuinely) surprised to know that's not because of display notation cut anything off it's because IEEE double floats are being used once it gets interpreted as a number and that's as far as the smaller digits in such a large number are accurately encoded for. https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/1... https://ask.libreoffice.org/t/decimal-precision-how-to-have-18-decimals/33491 https://ask.libreoffice.org/t/decimal-precision-how-to-have-...
  - justsomehnguy 4y ago
    I know and I don't care. Just leave my numbers as is.
Tagbert 4y ago
It’s not just a display issue. It will silently convert any digits past 16 into zeros with no way to retrieve them. Where i work, transaction records have an ID that is a 20 digit numeric code. If you are not very careful when importing that, pasting that, or editing cells, Excel will just strip out data because it’s priority is to convert it to a lossy format.
- zamadatix 4y ago
  That has to do with the backing number type in Excel being an double precision float not what the display convention of the value is.
coderintherye 4y ago
As Tagbert noted in their response, your suggested solution does not work. Imagine you have an 18 digit number that happens to end in a couple zeros and Excel "helpfully" converts this to scientific notation. Ok, you say, just set the number format, except then you don't get your original number, you get something else. This is because Excel (and Google Sheets) silently convert numbers. At least in Google Sheets if you explicitly Import a sheet you can uncheck the box to convert and it will come in as plain text, but you have to do that every time. So yes, I would prefer to just display long numbers. It's not paper and I can resize columns to fit.
- mNovak 4y ago
  So I think your complaint is really about the internal float storage truncating sig figs more than anything. I agree that for all its "smart" pattern matching features, Excel should be able to figure out when to give you a better storage precision, and handle it fluidly in the background. Seems like we could assume one doesn't import a ton of extra digits for no reason.

by mNovak 4y ago

justsomehnguy 4y ago

Yes, display long numbers. Exactly why this is not a sheet of paper - I can resize the columns all I need. Just tested in LibreOffice calc: 30937930503010769238 is mangled to scientific notation: 3,09379305030108E+19 . If I change the format to anything (but it's already a generic number) than it becomes 30 937 930 503 010 800 000.00 or something. And if I change the format of the cell to the text it becomes... 3,09379305030108E+19 again. And yes it's a number, though it doesn't get used as such in my case. If it was really used for anything of value - I would be really pissed for the 30k inaccuracy.

zamadatix 4y ago
You may be (genuinely) surprised to know that's not because of display notation cut anything off it's because IEEE double floats are being used once it gets interpreted as a number and that's as far as the smaller digits in such a large number are accurately encoded for. https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/1... https://ask.libreoffice.org/t/decimal-precision-how-to-have-18-decimals/33491 https://ask.libreoffice.org/t/decimal-precision-how-to-have-...
- justsomehnguy 4y ago
  I know and I don't care. Just leave my numbers as is.

Tagbert 4y ago

It’s not just a display issue. It will silently convert any digits past 16 into zeros with no way to retrieve them. Where i work, transaction records have an ID that is a 20 digit numeric code. If you are not very careful when importing that, pasting that, or editing cells, Excel will just strip out data because it’s priority is to convert it to a lossy format.

zamadatix 4y ago
That has to do with the backing number type in Excel being an double precision float not what the display convention of the value is.

coderintherye 4y ago

As Tagbert noted in their response, your suggested solution does not work. Imagine you have an 18 digit number that happens to end in a couple zeros and Excel "helpfully" converts this to scientific notation. Ok, you say, just set the number format, except then you don't get your original number, you get something else. This is because Excel (and Google Sheets) silently convert numbers. At least in Google Sheets if you explicitly Import a sheet you can uncheck the box to convert and it will come in as plain text, but you have to do that every time. So yes, I would prefer to just display long numbers. It's not paper and I can resize columns to fit.

mNovak 4y ago
So I think your complaint is really about the internal float storage truncating sig figs more than anything. I agree that for all its "smart" pattern matching features, Excel should be able to figure out when to give you a better storage precision, and handle it fluidly in the background. Seems like we could assume one doesn't import a ton of extra digits for no reason.