Ask Your Question

Show all numbers with specific exponent [closed]

asked 2015-12-17 15:51:58 +0200

Dor gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-28 01:02:44.234929

1 Answer

Sort by » oldest newest most voted

answered 2015-12-17 16:52:38 +0200

Lupp gravatar image

updated 2015-12-18 16:21:20 +0200

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:

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

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

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-12-17 15:51:58 +0200

Seen: 937 times

Last updated: Dec 18 '15