[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