[GTALUG] An LibreOffice question.
Stewart C. Russell
scruss at gmail.com
Sun Aug 5 17:26:46 EDT 2018
On 2018-07-30 03:46 PM, Alvin Starr via talk wrote:
> Does anybody know how to display and work with SI numbers like 10k or
> 20M or 40G within LIbreOffice?
I had a think about this, and came up with this function for text in
cell D2:
=IF(LEN(T(D2))=0, D2,CONVERT(VALUE(LEFT(D2,
SEARCH(" ",D2))),MID(D2,SEARCH(" ",D2)+1,1)&"m","m"))
This assumes you've used a single space between the value and the unit
prefix, as is SI-correct. Couldn't see an easy way of doing this without
the space, tbh.
The way the above function works:
* if the argument is a numeric value, pass it through
* if the argument is a string, return CONVERT(«numeric part»,
"«prefix»m", "m"). This is sorta misusing the unit conversion function
by going via metres, but it saves having a lookup table or custom function.
Examples:
Input Value
1 u 1E-06
10 u 1E-05
100 u 1E-04
1 m 0.001
10 m 0.01
100 m 0.1
1 1
10 10
100 100
1 k 1000
10 k 10000
100 k 100000
1 M 1000000
Note that this won't work with IEC 60027-2 binary prefixes.
> While I am at it how about engineering notation?
That now works under the normal Ctrl-1 format menu
More information about the talk
mailing list