One of the things that science/engineering/inventory spreadsheet users have been doing since the beginning is working their own unit conversions into their models. Yes, there is the CONVERT function, but that still requires input to be in specific units, and if you want to display units you either need an entire different column for listing the unit name or you have to use custom number formatting to add " kg" or " sec" to your value.
Due to the focus on low-code/no-code, my suggestion is to create a new Data Type, which I’ll call “Unit Value” for no better term for now. Here is how it might function:
Data Type Options:
a) Unit Type, with a drop-down that lists a huge range of options maybe as a categorized list or just the general category (mass, weight, volume, etc). Minimal options would include all of the units available for the CONVERT function. The value stored would be in the chosen unit, or a default base unit defined in the help system.
b) Displayed Unit Type, after choosing the unit type you would want to be able to choose which unit to show in the displayed values. The values displayed would include the unit but still be usable in calculations, just like using a custom number format in Sheets/Excel like 0.00 “kg”
c) Precision … like the # format, you could allow the user to choose the precision and an option for scientific notation (because significant digits are easier with scientific notation).
This is where the data type would gain its wow factor. When double-clicking on a value that uses this new data type, you could open a drop-down contextual window (like the date picker) that would let you choose a unit from the drop-down so that when you enter the value, you are entering it in your chosen unit. Then, after pressing Enter, the value is automatically converted and stored based on the Data Type option (and displayed based on the Data Type’s chosen unit type).
Similar to entering a date or a time, and even more similar to entering a Duration, other functionality could be the ability to type the desired unit and have SSDC automatically convert the value to the current cell’s displayed unit. For example, if the Date Type is Weight and it is displaying “# kg”, I may want to quickly type “5 lb” and I’d want it to be smart enough to convert that to kilograms if kilograms is the unit currently being displayed in that cell. If I entered the value “5 lb 2 oz” I would want that converted to decimal pounds or whatever the displayed weight unit is.
I’m currently working on a spreadsheet for inventory control and orders and I’ve found that tools like ShipStation/Shopify/Stamps.com make it very easy to choose whether to enter weights for products and packages in different units, and they convert the units internally as needed.
So, if I have an inventory spreadsheet and I want to enter weights, I may have some in ounces, pounds, pounds and ounces, decimal pounds, decimal ounces, kilograms, or grams. But, it’s very inconvenient to have multiple columns for these different input/display options, and it’s inconvenient to be doing these conversions outside the spreadsheet just because I’m limited to a particular unit for input. Instead, I’d like just one column to represent Weight and I’d like to be able to use the Data Type option to automatically convert those weights to whatever unit I choose to display, and I want the interface to let me enter the value in whatever weight unit is most convenient.
In essence, I guess this suggestion is an expansion on what the Duration data type was meant to be. But, instead of just Time, this data type could be used for any consistent unit of measure.