45 ms·

Excel is also super dangerous. All numbers are stored as double, which means that if you open a csv file that contains large int64 ids, they will likely be roun

by cm2187 4y ago

Excel is also super dangerous. All numbers are stored as double, which means that if you open a csv file that contains large int64 ids, they will likely be rounded. And then there are dates, and people who still think it is a good idea to store dates in a US or European format in a csv file rather than something unambiguous and universal (yyyy-mm-dd).

happyopossum 4y ago
That doesn’t sound like a dengerous excel problem, it sounds like a sloppy ETL problem. Don’t import data blindly - check the date formats for example.
- exadrid 4y ago
  That's nice in theory, but in practice people do it. https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
joshspankit 4y ago
That’s garbage. If I input a number in to a spreadsheet I expect that number to stay exactly as-is. That a Excel could introduce bugs due to doing floats behind the scenes.... Completely unacceptable.
- trelane 4y ago
  must be new here .. :)
  - joshspankit 4y ago
    Hahahaha I appreciate that comeback. Well played.
- Someone 4y ago
  Most programming languages don’t behave that way, either. Why would we have to hold Excel to higher standards? Now, that Excel stores binary floats, but tries to pretend to use decimal ones, that’s something I think we can blame it for. From section 2 of https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf: Some parentheses in Microsoft’s Excel 2000 spreadsheet possess uncanny powers: Values Excel 2000 Displays for Several Expressions Expression 1.23456789012345000E+00 <– Entered to help count digits V = 4/3 displays… 1.33333333333333000E+00 Does Excel carry 15 sig. dec.? W=V- 1 3.33333333333333000E-01 Whence comes the 15th 3 ? X = W*3 1.00000000000000000E+00 Where went all 15 of the 9s ? Y=X- 1 0.00000000000000000E+00 They all went away ! Z = Y*2^52 0.00000000000000000E+00 Really all gone. (4/3 - 1)*3 - 1 0.00000000000000000E+00 Yes, gone. ((4/3 - 1)*3 - 1) -2.22044604925031000E-16 (But not ENTIRELY gone !) ((4/3 - 1)*3 - 1)*2^52 -1.00000000000000000E+00 Excel’s arithmetic is weird.
  - joshspankit 4y ago
    > Why would we have to hold Excel to higher standards? Because one of it’s main uses is for doing calculations with money?
hakfoo 4y ago
I've seen a lot of compliance tests for credit card processing. These frequently will be Excel sheets with series of test descriptions. For example "Run a $12.45 sale on card number XXXXX with a tax amount of $1.73 and a shipping address of 1234 Main Street, 70242." Inevitably the card numbers are rounded. If you paste in a timestamp for the transaction result, that gets cast weirdly. I end up reworking the default formats for the sheet so that things will not be clobbered.

by cm2187 4y ago

happyopossum 4y ago

That doesn’t sound like a dengerous excel problem, it sounds like a sloppy ETL problem. Don’t import data blindly - check the date formats for example.

exadrid 4y ago
That's nice in theory, but in practice people do it. https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

joshspankit 4y ago

That’s garbage. If I input a number in to a spreadsheet I expect that number to stay exactly as-is. That a Excel could introduce bugs due to doing floats behind the scenes.... Completely unacceptable.

trelane 4y ago
must be new here .. :)
- joshspankit 4y ago
  Hahahaha I appreciate that comeback. Well played.
Someone 4y ago
Most programming languages don’t behave that way, either. Why would we have to hold Excel to higher standards? Now, that Excel stores binary floats, but tries to pretend to use decimal ones, that’s something I think we can blame it for. From section 2 of https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf: Some parentheses in Microsoft’s Excel 2000 spreadsheet possess uncanny powers: Values Excel 2000 Displays for Several Expressions Expression 1.23456789012345000E+00 <– Entered to help count digits V = 4/3 displays… 1.33333333333333000E+00 Does Excel carry 15 sig. dec.? W=V- 1 3.33333333333333000E-01 Whence comes the 15th 3 ? X = W*3 1.00000000000000000E+00 Where went all 15 of the 9s ? Y=X- 1 0.00000000000000000E+00 They all went away ! Z = Y*2^52 0.00000000000000000E+00 Really all gone. (4/3 - 1)*3 - 1 0.00000000000000000E+00 Yes, gone. ((4/3 - 1)*3 - 1) -2.22044604925031000E-16 (But not ENTIRELY gone !) ((4/3 - 1)*3 - 1)*2^52 -1.00000000000000000E+00 Excel’s arithmetic is weird.
- joshspankit 4y ago
  > Why would we have to hold Excel to higher standards? Because one of it’s main uses is for doing calculations with money?

hakfoo 4y ago

I've seen a lot of compliance tests for credit card processing. These frequently will be Excel sheets with series of test descriptions. For example "Run a $12.45 sale on card number XXXXX with a tax amount of $1.73 and a shipping address of 1234 Main Street, 70242." Inevitably the card numbers are rounded. If you paste in a timestamp for the transaction result, that gets cast weirdly. I end up reworking the default formats for the sheet so that things will not be clobbered.