Hacker News Reader

Comment by nico | Hacker News Reader

Now I noticed that the period at the end of the paragraph is a bit unfortunate

The value was 1. which I guess is shorthand for 1.0 and it’s technically not the same value as 1

On top of that, sometimes values like 0.961727 will be shown as 1, so sometimes you think that the value in the cell you are referring to is a 1 but instead it’s something close to it

In particular I was making a list of array positions from 1 to 32 and calculating x,y coordinates from the position using the formulas (x = (i-1) % width, y = (i-1)/width)

Some of the coordinates were wrong, and it was because the i values were not integers, which I couldn’t tell just by looking at the sheet, and only realized it when double clicked on the cells

View on HN

It's frustrating that there isn't an easy way to see the "canonical" value and format of a cell (in Sheets or Excel).

Just looking at a cell, it's not trivial to see if it's the number 1 or the string 1 (you can enter text by using a leading apostrophe, but that's not the only way to get text in a cell!). Numbers and strings have different alignments by default, but that can be overridden. The numeric value of the string 1 is 0 if you're using the SUM formula, but it's 1 if you use +. In other words, =A1+A2 does not necessarily equal =SUM(A1:A2)

Then you can format numbers however you like. For example, dates are stored in spreadsheets as days since an epoch (not the Unix epoch). So you can have the number 2 in a spreadsheet cell, then format it as a date, but just the day of the month, and it can appear as 1.

There's rounding, which bit you. 0.95 can appear as 1 if you display fewer decimal places.

Finally, there's the fact that the calculation is done like IEEE 754. Programmers are used to floating point numbers and the fact that properties like associativity don't apply, but that's not obvious to everyone.

You definitely know a lot about mangling data with spreadsheets!

> Programmers are used to floating point numbers and the fact that properties like associativity don't apply, but that's not obvious to everyone

One of my first programming assignments in college was to simulate a pool table. I had to go ask the TA what the hell was wrong with my code, the balls would never bounce because their distances to anything were never 0… fun times

Thank you!

Reply on news.ycombinator.com