How can I get LibreOffice Calc to calculate significant figures automatically?

English
, ,
#1

I’m using LibreOffice Calc for some repetitive scientific calculations, a good use of a spreadsheet, and it’s a bit annoying to have to figure out the significant figures manually on each repetition. I’d like to have a mode that I can put LibreOffice calc in that works like this:

  • When I enter a numerical/decimal/floating point value, it records the significant digits in some way (e.g. If I type in 5.00E10 it records that there are two significant figures.).
  • When it calculates a cell’s value based on a formula, it calculates as many decimal places as possible, but also stores the number of significant figures, using standard significant figure rules.
  • When a cell’s value is displayed, it rounds to the appropriate number of significant figures, but the full precision is used for subsequent calculations relying on that cell.

Of course, I’m open to other ways that this could be automated, but this seems like the best way.

#2

“…, using standard significant figure rules.”
How would you define these “standard” rules precisely to get a specification for the routines you want to be implemented?
The concept of (decimal) significant digits is only applicable as a “rule of thumb” imo.
How to apply it if a standard function must be calculated?
See also Interval arithmetic - Wikipedia .

#3

That mode in Calc does not exist.

To keep a record of significant digits based on the precision/length of the entry, I guess you would have to input the numbers as text and extract the value and precision as separate figures. The VALUE() function and some LEN(REGEX(...)) construct are likely candidates.

#4

— happened again, typed answer, accidentally hit a link, answer gone … is it possible to have a fallback as in bugs? —

@djohn49: like such ideas,

@Lupp: “standard rules”? if calc would consider:

  • ‘addition, sum, difference, mod, round results have max as many decimals as the max in the operands’ and
  • ‘multiplication result has max as many as the amounts in the factors added’ and
  • ‘other operations have 16 significant decimal digits while their 53-bit-dyadic-ULP is smaller than their 16-digit-decimal-ULP, else 15’ and
  • ‘round value! to that pattern, not only display’ and
  • allow the user to see all! digits, and
  • if displaying less digits than available in value mark it in display,
    would make calc a better program, users keep calm, and this world a better one,

@djohn49:
some time ago i cluttered together a macro trying to calculate the amount of ‘valuable digits’ (possibly valuable digits) in a figure, it suffers from multiple shortcomings (idiosyncratic rounding and rounding fails in calc, differences between ‘sheet’ and ‘basic’, … ) but at least ‘does something’, perhaps you can use and / or improve it?

@Lupp: - or others -
if you like to have a look in it and tell me where i can do better … highly appreciated!

function decimals_s(ByVal darg1 as double) 'inputs: "+/-", "xy", "xy0", "x,y", "0x,y0", "xE+/-n", "x,yE+/-n", "x,y00E+/-n"?, "x,y00hE+/-n", ...
'bs: 2020-11-16 - beware !!! under work !!! 'round' has fails, e-strings sometimes contain 'hidden value'
'evaluate different value representations about amount of decimal digits, gives max digits which may! contain valuable content, 
'2021-03-10: added handling of '0', 
'2020-11-28: new attempt with generalised handling of hidden value, 
'2020-11-21: continue checking, compare versions, 
if darg1 = 0 then
   decimals_s = -307 
else 
   if darg1 < 0 then darg1 = -darg1                              'negative values, solved? 
      if (instr(1,darg1,"E") > 0) then                               'scientific notation
      e_num = - val(right(darg1,(len(darg1)-instr(1,darg1,"E"))))  'cInt had problems with "+" sign, 
      if darg1 <> val(left(darg1, instr(1,darg1,"E")-1)) * 10^-e_num then 
         e_num = e_num + 2
      endif
      str_part = left(darg1,instr(1,darg1,"E")-1)
      str_dec = len(trim(str(str_part))) - 1
      if instr(1,str(str_part),"-") then                             '"-" is not a decimal place
         str_dec = str_dec - 1
      endif
      if instr(1,trim(str(str_part)),".") then                          '"." doesn't count as digit
         str_dec = str_dec - 1 
      endif
      decimals_s = e_num + str_dec
   else
      decimals_s = decimals_std_s(darg1)
   endif
endif
end function 'decimals_s


function decimals_std_s(ByVal darg1 as double)
'bs: 2020-11-21
'calculate amount of decimal digits in 'standard' strings, 
'2021-03-10: added handling of '0', 
'they don't 'hide content'? - they do :-(
'or if the do they are exchanged as e-strings ... i hope, have to check ... it depends :-(
if darg1 = 0 then
   decimals_s = -307
else   
   if (instr(1,darg1,"E") > 0) then                               'scientific notation
      decimals_std_s = "fail, scientific notation" 
   else   
      if darg1 < 0 then 
         darg1 = -darg1                              'negative values, solved? 
      endif
      dtemp1 = darg1 * 1e14 
      dtemp2 = int(darg1 * 1e14)
      dtemp3 = darg1 * 1e15 
      dtemp4 = int(darg1 * 1e15)
      dtemp5 = darg1 * 1e16 
      dtemp6 = int(darg1 * 1e16)
      if darg1 * 1e14 <> int(darg1 * 1e14) then 
         if darg1 * 1e15 <> int(darg1 * 1e15) then 
            if darg1 * 1e16 <> int(darg1 * 1e16) then 
               decimals_std_s = 17
            else
               decimals_std_s = 16
            endif
         else
            decimals_std_s = 15
         endif
      else
         if instr(1,str(darg1),".") then                             'decimal number
            decimals_std_s = len(trim(str(darg1))) - instr(1,trim(str(darg1)),".") + itemp1
         else                                                        'integer string
            decimals_std_s = 0
            do while right(darg1, 1) = "0" 
               decimals_std_s = decimals_std_s - 1
               darg1 = left(darg1, len(trim(str(darg1))) - 1)
            loop
         endif
      endif
   endif
endif
end function 'decimals_std_s
#5

  1. LibreOffice uses IEEE 754 64-bit floating point representation for all numbers, both integer and digital. See Floating-point arithmetic

  2. Decimal numbers like 0.1 and 0.2 cannot be represented exactly in binary systems. The decimal number 0.125 can be represented exactly as it is a binary (1/8).

This is how IEEE 754 64-bit, used on all PCs, represents 0.125 and the three following numbers. Note that 0.125 = 1/8 and is represented exactly. The next numbers, obtained by incrementing the binary by 1 digit each time, are forced to be approximations. It is not possible to represent any number between 0.12500000000000000000000000 and 0.12500000000000002775557561 and any calculation which results in such an between number will be forced to one or other representable number.

0 01111111100 [1]0000000000000000000000000000000000000000000000000000 = 1/8 = 0.12500000000000000000000000
0 01111111100 [1]0000000000000000000000000000000000000000000000000001 =     = 0.12500000000000002775557561
0 01111111100 [1]0000000000000000000000000000000000000000000000000010 =     = 0.12500000000000005551115123
0 01111111100 [1]0000000000000000000000000000000000000000000000000011 =     = 0.12500000000000008326672684

  1. IEEE 754 64-bit floating point has a precision of 53 bits or about 16 decimal digits.

  2. I think all spreadsheets use a non-displayed guard digit to ensure calculations do “what users expect”. That is, if you add three cells each with =1/3, Calc will always give 1.0000000000000000 and not 0.9999999999999999 because the result is rounded and the guard digit forces all the zeros.

  3. If you explore the limits of the number representation you will discover many strange and anomalous results - see Accuracy problems

  4. Calc documentation makes no mention of the method in which it uses numbers or the precision users can expect or its use of a guard digit so users are in the dark.

  5. Never attempt to check if two floating point decimal numbers are equal as they will invariably vary in the 14th or 15 significant digit. Always check if they differ by less an amount you consider appropriate in your calculation, or round them to fewer significant digits before doing the check, or use Tools > Options > Calc > Calculate > Precision as shown.

How to configure LibreOffice Calc to display a maximum of 10 digits for decimal numbers like Excel?
#6

‘IEEE 754 64-bit … precision of 53 bits …’ - that’s for operands, operations are weaker, e.g. 1E16 - (1E16-2) comes back with only one bit! and calc rounds that away,
‘guard digit’ - no, guard digit is a hardware feature, 1/3+1/3+1/3->1 results from correct rounding of bin figures with not too much deviation,
‘so users are in the dark’ - yes! )-:
‘they differ by less an amount’ - such is already done by calc, be aware that ‘double laxity’ may become as unpredictable as double rounding,
calc (and IEEE) suffer mostly from four effects, ordered by severity:

  • ‘cancellation’ at subtracting similar values,
  • people stating ‘fp-math is inaccurate’ and do not care for details,
  • a patchwork of ‘corrections’ making calculations unpredictable,
  • the available precision varies with the relation of the operands in their bin and dec ranges,
    for the OQ: calc can’t see the decimal digits in the binary strings, calc devs refuse to convert for intermediate rounding

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.