← Back to context

Comment by nico

1 year ago

Or when your formulas on Google sheets start failing because you do 1-1 and the result is not 0, and then you spend 30 minutes creating new sheets, searching the web and double checking everything, until you realize that the value on the cell wasn’t 1, but 1.

This happened to me yesterday

6 comments

nico

Reply
boppo1  1 year ago

Can you elaborate?

  • nico  1 year ago

    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

    • jkaptur  1 year ago

      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.

      1 reply →