(Bug) Exporting power values of 2
Closed this issue · 4 comments
Describe the bug
Even if the display on the Google sheet is not precise, the export of power values of 2 are correct until 2^54 and become false after that point.
To Reproduce
Steps to reproduce the behavior:
- List index from 0 to 63 on one column (called IndexCell)
- Create the formula "=Formula(2, IndexCell)"
- Export or Visualize data
- See error on index 55
Expected behavior
Power values of 2 should be correct until 2^63.
Interesting. ESD should be pulling the value straight from the sheet and I thought that E notation would translate as well. I wonder if this is a sheet data retrieval issue, a JavaScript number issue, or something else. I'll look into this. Good catch!
After running a few tests, I think this is a rounding issue with sheets. I duplicated your steps and got the same value. I manually entered values as well to be sure, but got the same result. Based on your screenshot and some other tests I ran, the expected value there is 36028797018964000
instead of 36028797018963970
correct?
I ran the exponential number through some E notation calculators to double check my work and despite some rounding weirdness it seemed like the 36028797018964000
value was the more standard result. I'm not sure if the Sheets POWER function is rounding more precisely or if it has a rounding error, but either way this seems to be related to Sheets, not ESD interpreting the value incorrectly. As such, I'm not really sure what can be done here unfortunately.
Sorry for the late reply!
Actually the expected value of 2^55
is 36028797018963968
(compute with this calculator).
The google sheet is starting rounding values from 2^50 however ESD still is correct until 2^54.
Based on that difference, the issue may comes from the javascript code itself.
This hypothesis seems to be confirmed by trying to compute power of 2 on this console, it was possible to reproduce the issue on 2^55.
After quick search I find this post which is interesting but without any solution
Now I'm the one with the late reply!
It's interesting that the JavaScript code itself is what causes the imprecision, but considering that it uses floating point numbers that's not too surprising at large numbers like 2^55
. That does reinforce my concern that I can't really do much about it since ESD can only pull data from Sheets as is. The solution here seems to be writing a high precision calculator to evaluate function expressions instead of pulling in reported values from Sheets directly... Which is a bit of a lift.
Unfortunately, that means this is probably an issue that I can't fix, or at least would not get around to for quite some time since building a calculator is outside the scope of ESD's functionality. I'm going to close this issue for now but if a good solution is found later I'll reopen it and see what I can do.