Calculation Inheriting Data Type formatting not always what you want

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.

1 Like

Yes this is a defect. From an Excel/GSheets compatibility ( and common sense ) perspective, the behaviour I would expect is as if A1 & A2 cells are formatted as Numbers ( with zero decimals) in Excel and B1 displays 0.01416666667 while still being Automatic.

Tracking as issue # 001583

1 Like

Agree. Very annoying. Column formula is one thing, but should be able to set the format as well, otherwise a bit frustrating. I need to resolve it by cell level formulas and spreadsheet.com is all about column types. Where could I possibly find the issue tracker to see progress?

Thanks and welcome @Jakub_Lambrych, we agree here as well. This issue is still being tracked as 001583. We haven’t gotten to it yet but raising priority now. We’ll update this thread as soon as we have progress to report.

In the meantime one way to override this behavior is to edit the data type of the cell. In the example above, right click cell B1 and choose “Edit data type”, select “Number” and the desired # of decimal places.

image

Separately we also have significant improvements planned for Column Formulas, allowing selection of an explicitly defined output data type with formatting settings.

1 Like

Thank you @matt for working on this :slight_smile: Yes, I work the way you suggested.

Cell level formula type promotion has been addressed in the May 22, 2021 product update.

Column Formula data type output control is planned in the short to medium term roadmap.

1 Like

This issue is now addressed with the new Formatting options available in the Column Formula data type dialog. Details in our September 18th release notes.

The new formatting option for column formulas is very easy to use. So far, it is working for all the cases where I’ve needed it (date, ratios, percentages, currencies).

3 Likes