Assume I have many numbers which are presented like so:
12.3E-6
or 45.6E-7
or 78.9E-8
How do I change all of them to be presented such that the exponent has a specific number? meaning:
Assume I want to present all numbers as micro (10^-6), so:
12.3E-6
or 4.56E-6
or 0.789E-6
How do I do that with Libreoffice Calc?
Thank you…
Within a certain range of values from 1.000E-6 to 999.9(some)E-6 this can be done using the ‘Engineering’ variant of the ‘Scientific’ number format set to an appropriate number of digits in the fractional part. (The feature was introduced with version 5.0.)
There is no way to set the exponent to be used to a fix value via formatting. If you urgently need your values displayed with an exponent of -6 (“micro”) in every case, you have two options as far as I can see, both coming with a disadvantage:
Disadvantage: Forego the numeric type. Use =TEXT(A3/1E-6,"0.000000")&"E-6" or something replacing the reference A3 with the term you want. (Obvious adaptions may be needed.) The result is of type text. Due to the (often misleading) behaviour of spreadsheets to automatically convert text occurring in the syntactic position of a number inside an arithmetic expression into numeric values if applicable, you can nonetheless reference the results for further calculations.
Disadvantage: Changed value. Calculate *Something*/E-6 instead of Something and stay aware of the fact that you have to multiply any reference to the result with 1E-6. You also may keep the unchanged value for referencing in a helper cell (possibly hidden) instead. If you also want to display the exponent part “E-6” together with the “how many micros” number, simply add " E-6" as a string constant to the format code. Stay aware in this case of the fact that the display will be lying then about the true value of the cell. The displayed exponent string will not influence the referenced value.