Utilize SI units in Google Sheets

Question

Seven years ago this was asked, are there any new developments with regard to formatting with SI units?

Normally, I will adjust the format to be all milli or micro simply by division, however my most recent proof of concept blended the applicability of milli and micro such that data set runs across the milli and micro boundaries.

Dealing with internal combustion engine ignition timing has been an unusual experience in my engineering: how RPM can vary from roughly 900 to as high as 14000 has meant being continuously aware of the capacity of data types.

In this specific case, there is a recovery time which is a fixed value. It limits the maximum RPM of this system, but it alone cannot dictate maximum RPM as the functionality I'm testing has its own dwell independent of recovery time. Staring at the wall of numbers makes it difficult to grasp the magnitude of the values and make interpretations of the data.

Sample data set arranged vertically. Gray are the equations in those cells and green are my ideal formatting for those cells.

RPM is continuously varying and the Duty Cycle (aka dwell) is programmable. In this sheet, I'm verifying observed timing and trying to relate it to other constraints.

Answer 1

Custom number formatting supports conditionals, but only two of them plus a default format. So, to display millions and billions of dollars, you can use Format > Number > More Formats > Custom number format with something like this:

[>999999999.999]$#.00,,, "B";[>999999.999]$#.00,, "M";$#,##0_M

Unfortunately, this will only look pretty when your values cover a couple of orders of magnitude.

To cover the whole SI prefix range, you can convert the values to text in another cell or column like this:

=arrayformula( 
  if( 
    isnumber(A2:A), 
    ifs( 
      abs(A2:A) >= 10^27, text(A2:A, "0.0##e+### "), 
      abs(A2:A) >= 10^24, text(A2:A / 10^24, "0.0## ""Y"""), 
      abs(A2:A) >= 10^21, text(A2:A / 10^21, "0.0## ""Z"""), 
      abs(A2:A) >= 10^18, text(A2:A / 10^18, "0.0## ""E"""), 
      abs(A2:A) >= 10^15, text(A2:A / 10^15, "0.0## ""P"""), 
      abs(A2:A) >= 10^12, text(A2:A / 10^12, "0.0## ""T"""), 
      abs(A2:A) >= 10^9, text(A2:A / 10^9, "0.0## ""G"""), 
      abs(A2:A) >= 10^6, text(A2:A / 10^6, "0.0## ""M"""), 
      abs(A2:A) >= 10^3, text(A2:A / 10^3, "0.0## ""k"""), 
      abs(A2:A) >= 10^2, text(A2:A / 10^2, "0.0## ""h"""), 
      abs(A2:A) >= 10^1, text(A2:A / 10^1, "0.0## ""da"""), 
      abs(A2:A) >= 10^0, text(A2:A / 10^0, "0.0## """""), 
      abs(A2:A) >= 10^(-1), text(A2:A / 10^(-1), "0.0## ""d"""), 
      abs(A2:A) >= 10^(-2), text(A2:A / 10^(-2), "0.0## ""c"""), 
      abs(A2:A) >= 10^(-3), text(A2:A / 10^(-3), "0.0## ""m"""), 
      abs(A2:A) >= 10^(-6), text(A2:A / 10^(-6), "0.0## ""µ"""), 
      abs(A2:A) >= 10^(-9), text(A2:A / 10^(-9), "0.0## ""n"""), 
      abs(A2:A) >= 10^(-12), text(A2:A / 10^(-12), "0.0## ""p"""), 
      abs(A2:A) >= 10^(-15), text(A2:A / 10^(-15), "0.0## ""f"""), 
      abs(A2:A) >= 10^(-18), text(A2:A / 10^(-18), "0.0## ""a"""), 
      abs(A2:A) >= 10^(-21), text(A2:A / 10^(-21), "0.0## ""z"""), 
      abs(A2:A) >= 10^(-24), text(A2:A / 10^(-24), "0.0## ""y"""), 
      true, text(A2:A, "0.0##e+### ") 
    ) 
    & 
    "g", 
    A2:A 
  ) 
)

The formula should go to row 2 of a free column. It shows weights in grams. To use another unit, replace the "g" with something else.

Outside the range 1e±24 the formula will use scientific notation.