Google Sheets – How to Neatly Format Length Across Multiple Orders of Magnitude

google sheets

I have a spreadsheet with a column for length with values ranging from millions of kilometers to fractions of micrometers. Viewing it all in meters as unformatted numbers directly is awkward since you have to count a lot of zeros to know what scale each thing is, and this sheet needs to be easy for many people to understand.

Is there a way to use number formatting to automatically display a sensible number followed by the SI notation (at least km, m, and mm, larger range optional)? For example:

  • 0.001 -> 1 mm
  • 0.5 -> 0.5 m
  • 1 -> 1 m
  • 100 -> 100 m
  • 250 -> 250 m
  • 5000 -> 5 km
  • 1000000000 -> 1000000 km or 1 Gm

This information is used by other cells, so must be stored as a number (i.e. I can't just write the appropriate SI notation with letters in the cell).

Best Answer

Putting the data in one column and having a separate column for display with this formula:

=if($Q4>=1000000000,Q4/1000000000&" Gm",if($Q4>=1000000,Q4/1000000&" Mm",if($Q4>=1000,Q4/1000&" km",if($Q4>=1,Q4&" m",if($Q4>0.01,Q4*100&" cm",if($Q4>=0.001,Q4*1000&" mm",if($Q4>=0.000001,Q4*1000000&" µm",Q4*1000000000&" nm")))))))

displays lengths with an appropriate unit for everything between a nanometer and a gigameter.

It's not perfect since it requires two columns (separating input column from display), but it solves the main problem.

Related Topic