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

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.

edit retag close merge delete

1

"..., 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?

( 2021-03-05 00:07:48 +0200 )edit

Sort by » oldest newest most voted
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.

more

'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 ...(more)

( 2021-03-11 01:49:15 +0200 )edit

--- 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,
'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,
'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 <> ...
more

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.

more