2

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).

Improve this question
2
  • @pnuts That's what I've settled on so far (custom formula: #.#E+#0" m"), but it would be more clear to readers to have different units displayed at different scales if possible.
    – plex
    Commented May 19, 2014 at 13:51
  • hm, yea, splitting data and display would work. I came up with this: =choose(if(D4>=1000,1,if(D4>1,2,3)),CONCATENATE((D4/1000)," km"),CONCATENATE(D4," m"),CONCATENATE((D4*1000)," mm")) which works, and can easily be extended to other SI units.
    – plex
    Commented May 20, 2014 at 3:12

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.