Ask Your Question

calc: decimal places: evaluate in macro, challenging!, number or 'scientific string'

asked 2020-11-15 18:20:33 +0100

newbie-02 gravatar image

updated 2020-12-01 17:35:40 +0100

hello everyone,

i tried heavily and searched the web to no avail ... :-(

short: doe's someone have a macro, formula, function or similar at hand which computes the significant 'after dot' - decimal - digits of a value even for tricky cases where e.g. 8,1234678901234600E-004 (which is rounded for display by calc) contains some hidden value in the 16 (and 17?) digit and represents the real value of 8,123467890123456(1)E-004?


i have rarely 'stood so beautifully on the tube' ... '=X-ROUNDSIG(X;3)' ... is very close to what i need, already in the sheet, without macros, for small rounding arguments roundsig will hopefully work correctly, if not it will hopefully be fixed when fixing the other rounding functions,

above just needs some extra math if roundsig is rounding up, the result will have the opposite sign, but i guess i can get that 'programmed',



  • deleted the macro as ... 'oh my god what kind of crap have i been working up', and above looks better


long: for a little project i deal with precision of calculations in calc, and need to know how many significant decimal digits a value has, (digits carrying value trailing zeroes not counted),

normaly it's quite easy, 1,234 has 3 decimal digits,

i'd solve that in a basic macro by evaluating the string, if there is a better way i'd be thankful,

a little more challenging: 123400,0 has -2! decimal digits - for my use case - solved by string evaluation,

next step: 1234,567E+2 is 1234,567 * 10^2, is 123456,7 and has one significant decimal digit, solved,

small step inbetween: 1234,567E-2 is 1234,567 * 10^-2, is 12,34567 and has 5 significant decimal digits, solved,

but now it comes: 1,12345678901234E+20 has !! -20 !! decimal places (comma shift) from the E+20 but 14 of them are filled with digits and thus 'not free', result -6 even that is computeable,

and ... - following samples may be inexact - 1,12345678901234E+20 may or may not! carry some 'hidden value', two more digits which one can't see but compute ... i managed to get access to theese digits by 'rawsubtract' or simply subtracting a truncated 'upper part' from the value, e.g. 1,12345678901234E+20 - 1,12345678000000E+20 may result in '9,01234E+11' - no 'hidden value', or in '9,012341111321E+11' from which 901234 is the 'initial visible part', and '1111321' is the 'hidden value' (of which only the first two digits are significant, the first is correct (if not messed up by calc rounding) and the second is somehow granular as not exactly computed / computeable but has valid information about the value of the number, the rest is rounding noise / white noise,

i need: 1,12345678901234(00xyz)E+20 - (00xyz) are the hidden digits / noise - to evaluate to '-6' decimals, 1,12345678901234(40xyz)E+20 to evaluate to -5 decimals, and 1,12345678901234(03xyz)E+20 to '-4' decimals,

similar for negative exponents, 1 ... (more)

edit retag flag offensive close merge delete


critical will be what kind of storage LO uses in memory to store data. That I don't know, what i've come across elsewhere is that there exist a standard for floating point storage of numbers (IEEE-754), check this to discover how real numbers are stored in a binary way +/-x^y, +/- x and y occupy a specific sequence of bits in a 32 or 64 memory adress.

parsely gravatar imageparsely ( 2020-11-15 21:13:21 +0100 )edit

@parsley: thanks a lot ... but ... rounding to decimal borders can't be done in binary representation ... imho ...

newbie-02 gravatar imagenewbie-02 ( 2020-11-15 21:45:23 +0100 )edit

yes, true and by design a computer works with binary floating point data, so it forces us to convert binary precision to decimal precision. in a calc sheet A1=0 : A2=A1+1 A3=A2+1 / B1=1 : B2=B1 * 2+1 : B3=B2 * 2+1 . Adjust both rows for numeric format. Copy formulas up to e.g. row 57.
Then at row 53 (a decimal represented number with 16 digits) the multiplication +1 is no longer exact: indicating that calc switched from exact integer representation of data, to floating point representation of data. This will also be the case if an uneven number is divided by two.
For a 64 bit binary represented variable, 52 bits are for the mantissa, 11 for the exponent and 1 for the sign of the number. By 1 bit difference? in line with the findings here.
From the calc sheet you see that ...(more)

parsely gravatar imageparsely ( 2020-11-16 10:39:27 +0100 )edit

@parsley: i know about theese things, and i'm just asking for a possibility to get the correct number of decimal digits, e.g. hidden in '=2000000000+0,00000875', displayed as '2000000000,000010000' or '2,00000000000001E+09' (scientific), thus truncated and inaccirate, but containing most of the original info as you can see with '=[cell]-2000000000' in another cell,
with above formula you see: '0,000008821' from which '0,000008' is valid exact content, '8' is 'fuzzy but still significant, and '21' is 'fp-nonsense', at the moment i need not to access the value of theese digits, just get to know that they are there and include them in the arguments for rounding, to not to accidentally round away accuracy, while being able to round away as much fp-fractals as possible,

newbie-02 gravatar imagenewbie-02 ( 2020-11-16 20:13:54 +0100 )edit

The determination of the uncertainty in a calculated value according to some formula with a number of arguments is the subject of numerical analysis. The uncertainty in a result is dependent upon the formula at hand,
It is inevitable that the representation of numbers has a limited amount of significant digits, for calculation of uncertainty you are allowed to take one digit extra.
How is a relative uncertainty of 10^-16 related to the real world? What i remember from school is that uncertainties are significant in measurements (10^-3 is quite good) and in the case of calculations in interative loops, where they can sum up.

parsely gravatar imageparsely ( 2020-11-16 23:12:29 +0100 )edit

@parsley: hey! that pdf looks very nice for my ideas of explaining fp-inaccuracies ... :-)
i call it 'absolute' vs. 'relative' fail (or precision) of a result, but it's the same issue to understand! - thanks a lot,
the inaccuracies of calcs (or other spreadshets) fp calculations are! important as 'spreadsheets rule the world', and as they are unknown to most users, and as they sometimes 'blow up' in 'ill conditioned' calculations, thus if it's possible to overcome them that could be a 'good thing',
for my idea i need to know about the 'decimal precision' of the operands therefore the amount of decimals in a number,
!! and it's quite hard to calculate that from 'e-string' notations varying between 'sheet' and 'basic' and hiding parts by rounded display!! :-( - see macro added to question,
('decimal precision': 1,5 is not! '1,5' but represents a range [1,45 .. 1,54999~] and ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-11-17 07:49:41 +0100 )edit

OK that's a result, to comment on your original question "does someone have a macro, formula, function or similar at hand which computes the significant 'after dot' - decimal - digits of a value ", such a macro is hard/impossible to accomplish, because it is dependent upon the formula, so it comes down to sitting at a desk and work it out with pencil and piece of paper. eg: calculating 1/T1 - 1/T2, where T1=300 and T2=310, is common in chemical engineering and hard to calculate (in the 70's with only logarithmic tables), but can be rewritten as (T2 - T1) / (T1 * T2). Which can be evaluated more precise.
That's what some parts of numeric mathematics are about, especially in making stable iterations that are fast and accurate.
Fractal mathematics were discovered by the observation that stopping an iteration and starting it again yielded different results, no ...(more)

parsely gravatar imageparsely ( 2020-11-17 08:48:17 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-11-17 13:03:11 +0100

AlderOak gravatar image

updated 2020-11-21 21:27:51 +0100

Read Floating-point arithmetic for everything you want to know.

Do you realise that obsessing about the 17th significant digit is like measuring the distance from London to New York to a resolution of about one thousandth of one millionth of an inch?

What are you working with which requires that accuracy?

Be sure you understand the difference between resolution and accuracy.

IEEE 64-bit FP arithmetic handles numbers from 10^-307 to 10^308. You need to remember there are only about 10^80 particles in the known universe so this allows counting the particles in 10^228 universes.

Adjacent numbers are separated by different "distances", where the difference is "one bit in the mantissa to the power of the exponent". Adjacent small numbers thus have smaller distances between them; adjacent large numbers have larger distances between them.

See numbers.odt where you will see that 2^100 displays 15 significant digits followed by 16 zeros. Hence, the adjacent numbers differ by plus and minus 1x10^16 respectively.

Similarly, 2^-100 displays as 0.00000000000000000000 unless you use scientific notation in which case it displays as 7.88860905221012000000E-31, ie 15 significant digits followed by 6 zeros. The adjacent numbers differ by plus and minus 1x10^-24 respectively.

Calc allows you to display 20 digits after the decimal point but only fifteen are significant and the last five are set to zero. The CPU hardware uses a non-displayed digit to force the rounding so that 1/3 +1/3 + 1/3 = 1.0; and not 0.999999999999999.


See Floating-Point Reference Sheet for Intel® Architecture for Intel PCs. It describes in full how calculations are performed and the use of the extra bit to force correct rounding.

Edit 1 in response to Comment

=ROUND(81000000000000000000;7) asks for 81000000000000000000 to be rounded to 7 places. Calc v6.4.4.2 gives me the correct 81000000000000000000 answer.

Search for IEEE conversion sites like IEEE 754 Calculator where you can see what is happening in a 64 bit FP calculation.

Base Convert: IEEE 754 Floating Point shows that 81000000000000000000 is stored as 81000000000000000000 in IEEE 64 bit. So are all numbers up to 8100000000000000512. It is only when you get to 8100000000000000513 that the stored number changes to 8100000000000001024. There is no way to store a number between those values.

IEEE-754 Floating Point Converter is only 32 bit but it shows how, when 81000000000000000000 is stored as a 32 bit number, it is actually stored as 81000001271467343872.

Edit 2.

Carefully read Accuracy problems at Floating-point arithmetic which describes lots of anomalies including some associated with rounding. I quote:

The fact that floating-point numbers cannot precisely represent all real numbers, and that floating-point operations cannot precisely represent true arithmetic operations, leads to many surprising situations. This is related to the finite precision with which computers generally represent numbers.

Also don't forget that operations are not done in the same way you did them at school. Divide is almost certainly done by ... (plus)

edit flag offensive delete link more


hello @AlderOak,

thanks for your help,

i don't need such precision, although it has awarded other people the nobel prize (gravitational waves),

i won't try particle counting with calc, i saw it stuck from on ;-)

i just want to clarify my bad feeling that too often calc has errors 'bigger than acceptable' for 'simple minded users',

'=ROUND(81000000000000000000;7)' for example should not! result in 8100000000000001,

i'm in doubt that such is 'fp-math unavoidable' or due to CPU-errors, with older versions of calc the result was correct,

to better deal with such problems i need a clear view of what is being calculated, not 'errors hidden under mayonaise' (rounding),

especially basic functionalities, which other functions rely on, should calculate correctly,

that given 'decimal-safe' calculations may be possible with calc - imho,

btw. 'last five are set to zero' doesn't hold, see '0,00000000000000012345',

newbie-02 gravatar imagenewbie-02 ( 2020-11-19 10:54:32 +0100 )edit

See Edit 1 in response to Comment

"such precision ... has awarded other people the nobel prize (gravitational waves)"

I am quite sure that the LIGO Gravitational Waves team does not use Calc for its calculations as, excellent though Calc may be for calculating their travelling expenses, they realise it is quite useless for their work.

They will use programming languages like C++ and Fortran which can calculate with quadruple precision 128 bit numbers.

Your requirements decide the tool you need to use. Using a tool at the limits of its accuracy, resolution and/or precision is a recipe for incorrect results.

AlderOak gravatar imageAlderOak ( 2020-11-19 12:45:01 +0100 )edit

@AlderOak, thanks again,
sorry a mysterious typo made it into my comment, it's not '=ROUND(81E18;7)' bothering me but '=ROUND(81E14;7)',
there are plenty of other values affected, e.g. '=ROUND(5000000000000001;9)' results in 5000000000000002 (smallest yet spottet),
once you know what to search for it's easy to find more cases, in the standard use of complex sheets things like this go unnoticed, but may cause bigger - even catastrophic - fails ... :-(
to deal with such things i'd like to have proper rounding, for that i need proper decimals, for that proper rounding, already a kind of deadlock, and for to see where all this mess evolves from i'd like access to the values taken for calculation, not the prettyfied display string ...
might be i just pinpointed one elementary fail: '=7E15/1E5*1E5' shouldn't be different from '=7E15*1E-5/1E-5', but is ... :-( (it ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-11-19 23:29:34 +0100 )edit

I think for that was added ROUNDSIG() function, using it with 15 as the number of significant digits seems to solve the issues of above comment. IMHO is my responsibility to use a tool as it should be

m.a.riosv gravatar imagem.a.riosv ( 2020-11-20 23:44:30 +0100 )edit

becoming long :-(
but moving things forward? :-)
1) limitations - (IEEE 16200000000000000 'granularity' is '2')
shouldn't gloss over:
2) bugs - 8100000000000001 is weakness, calc ver 4.1 did better, see tdf#138360,
and shouldn't stifle the quest for:
3) improvement - '=2,01 - 2', resulting in 0,01 instead of a rounded 0.0099999999999999979000 would also help for 'use as designed',
4) imho there are some weaknesses in 'the way' calc uses IEEE and fp-math, e.g.
5) calc sometimes boosts the granularity by a '4-bit-tie to zero' and further to 15 digits for the UI,
6) there are many ways ... e.g. other software, i'd like to 'improve calc', for me and! for others,
(for this i need to distinguish between 1) and 2), and access to the 'hidden digits' would help),
7) i wouldn't have spotted 2) if i didn't think the way ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-11-21 12:19:50 +0100 )edit

i tried to use roundsig as a crutch, it failed too, e.g. for '=ROUNDSIG(8000000000000001;17)', i suppose because of a calculation like 'times 10' (to get the 17th place before the comma), '+0.5' (rounding), 'integer of it', 'divide by 10',
might work better with 'take the first 17 digits, see if the 18th is greater than 4, then 17th plus 1, done',
for such solutions I would like to have an easy access to the hidden digits,
for the record: roundup and rounddown also make mistakes,
for the records 2: it happened again, i copied '=ROUNDSIG(8000000000000001;17)' from calc, (ctrl-c), pasted it in 'ask' (ctrl-v), and spotted a little later '=ROUNDSIG(80000000000000000001;17)', no instant repro, will investigate later,

newbie-02 gravatar imagenewbie-02 ( 2020-11-21 13:43:06 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-11-15 18:20:33 +0100

Seen: 80 times

Last updated: 8 hours ago