In the engineering disciplines int's nice to be able to have values formatted in easily readable engineering notation, where the exponent is a multiple of three. How can this be accomplished in Excel?
2 Answers
In the cell formatting options create a new custom type for ##0.0E+0.
To display a value with prefixes instead, one might do some formula massaging and end up with e.g.
=LEFT(TEXT(A1,"##0.0E+0"),FIND("E",TEXT(A1,"##0.0E+0"))-1) & MID("npum kMGT",5+VALUE(MID(TEXT(A1,"##0.0E+0"),FIND("E",TEXT(A1,"##0.0E+0"))+1,100))/3,1) &"s"
... for a value in A1, that represents a time in seconds (as indicated by the last "s" in the formula).
