Standardize use of units via custom cell formats

[bthuronyi]

For cells that contain values with units, Sheets can use a custom number format to add text units to the displayed value. Example: custom number format adds the microliter units, but the actual value in the cell is 50.

This looks nice, but can be confusing to users because they might think they need to type in the units. We can enforce compliance by using data validation (custom formula, =ISNUMBER(cell) ) and having the pop-up explain that units are shown automatically -- it just might be a little jarring for people at first.

On the other hand, including the units can increase accuracy, because it avoids the problem of users not noticing the units in the header and using the wrong ones. If the header says Time (min) and a user enters 1 meaning 1 hour, they are much more likely to notice this if the cell displays "1 min" after they type the 1 in. It would also help Asst: sheets make clear what users are supposed to pipette.

Custom formats don't impact data processing/filtering -- the value stored in the cell is just the number part.

Whichever approach we pick, we should implement it consistently across all numerical user-entered fields. Right now most fields do NOT have custom number formats for units but a few do.

[santiagochrist]

I think for me it would depend on whether there is already a clarification for units elsewhere, e.g., a header says something like "Default: 100 uL."

However, I do like the idea since it adds an engineering control in case the protocol is misread, the pipette is not adjusted, etc.

[bthuronyi]

I think for me it would depend on whether there is already a clarification for units elsewhere, e.g., a header says something like "Default: 100 uL."

However, I do like the idea since it adds an engineering control in case the protocol is misread, the pipette is not adjusted, etc.

There should be units in the column titles in header 2 no matter what, I think. We will want to retain them even if we use custom number formatting since the column title is shown/used in other places and needs context, plus sometimes the custom number formats won't be shown for the data.

[evelynqi]

I am in favor of the custom cell format, especially with the data validation. I think it makes it clear what the units are and it's great that it does not interfere with data processing/filtering. I also think that without data validation, users would recognize a mistake when it says 50 µL µL if they inputted 50 µL instead of 50.

[ethanjeon]

users would recognize a mistake when it says 50 µL µL if they inputted 50 µL instead of 50

agree !

[santiagochrist]

Applied it to the format for pipetting formula instead:

=if(vol="","",to_text(ifs(vol<2,text(vol,"0.000 µL"),vol<20,text(vol,"0.00 µL"),vol<200,text(vol,"0.0 µL"),TRUE,text(vol,"0 µL"))))

I did not concatenate the µL at the end since that would cause a blank volume to read " µL" instead of "", which may be pretty confusing.

[bthuronyi]

Unfortunately I had to roll back the format for pipetting change. Too many formulas are looking at these values and trying to use them as numbers. We can implement later with some adjustments