I ran into a situation today where I have a column of numbers that are integers: Data Type = Number with 0 decimal places displayed.
When I did a simple calculation to divide two of these numbers, the result was just 0. This was very confusing for a while until I realized that the calculation was inheriting the data type of the references.
Example: A1 = 34, A2 = 2400
Calculation: B1 = A1/A2
Even though cell B1 was still an “Automatic” data type, instead of showing me a decimal value, it was rounding to 0 (because that was the format of cells A1 and A2).
In some situations, it may be useful to inherit the data type of the reference, but perhaps to avoid confusion, the “Automatic” data type should display results based only on the value itself rather than inheritance from a formula’s references.
One thing I find myself doing in Excel all the time is converting values to the General format using the CTRL+SHIFT+` (tilde) keyboard shortcut, often just to check if a date is really a date (because doing this will convert it to its numeric datetime value), or to check if a value is rounded or not, or in other words to check the actual value stored in a cell compared to what is currently displayed. The formula bar displays the date or the formula, so that’s not always helpful.
What I would recommend for providing a quick check on whether a cell is text or a number would be to use the status bar in the bottom right corner of the window to display the current value (where Excel and GS display things like the sum of selected cells). Something like “Cell Value = 0.12345343”. This would be REALLY useful while debugging and likely not very hard to implement.