issue with rounding small! values, round to more than 20 decimals,

English
, , , , , , ,
#1

hi everyone,

it may be that i am a little blind today :frowning:

i want to round numbers, but very small ones,

for example 6.23455E-27 which has 32 decimal places to 6.235E-27 which has 30 decimal places,

‘round’ fails with error messages (Err:502 on screen, IllegalArgumentException in macro) as soon as i enter as second argument a greater number than 20,

this makes sense for numbers in decimal notation as calc is limited to 20 decimals on screen, but not for ‘scientific’,

what do i need the nonsense for? i want to try an automatic rounding to avoid fp-errors, and this can happen as a side effect,

i know that i could write my own rounding routine, but it would be nice if i could do it somehow ‘with onboard tools’,

it’s not! about getting that to work on the screen / in the UI (user interface) but to be able to use it in a macro,

as the basic language for macros seems to not to have a rounding function i made that from uno? available with:

function round_a (ByVal darg1 as Double, iarg2 as Integer)
'make round functionality accessible from basic macros, 
oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess")
round_a = oFunctionAccess.callFunction( "ROUND", array(darg1, iarg2)) 
end function

but that fails for arguments above 20 as well as ‘onscreen use’ of ‘=round(6.23455E-27; 21)’,

anybody an idea?

#2

Might you reconsider your omission of letter case and the insertion of unhelpful empty lines/paragraphs?
The software already limits the width. I tend to lose the survey over long threads with many empty lines.
The software already doesn’t very clearly allow to distinguish full stops from commas for me (probably it’s Firefox related).

#3

@Lupp: whowwww … !aua!,
which is the bigger the problem? my individual interpretation of graphically appealing and clearly arranged typesetting?
or the idiosyncratic interpretation of basic mathematical rules by calc?
let’s make a deal:
i’ll write in clean english, with! capitals, and correct paragraph formatting …
as soon as calc produces mathematical correct results!

P.S. reg. the lack of capitals … a friend of mine installed a matrix screensaver for me some time ago, and i broke it as i tried to disable - ‘b. hat die matrix kaputtgemacht’, - since then capital letters have become scarce
:frowning:

#4

(I only suggested considerations.)

The ROUND() function has a bug. It applies a constraint which is not specified, and also not justified by needs (due to limitations of IEEE 754 e.g.). That’s it.
There isn’t any basic mathematical rule violated insofar. Where ROUND() isn’t capable of doing what it was asked for, it reports the applicable error.
Once having the bug fixed, ROUND() will still need to report errors if it is asked (e.g.) to round a value to 309 decimal places because the representation of numbers as Double cannot handle this low order of magnitude. The acceptable scale factors are 2^-1022 through 2^1023.
Meanwhile you know workarounds either relying on a user function or by slightly complicated formulae.

The bug may not be fixed, however, as long as it wasn’t reported to bugs.documentfoundation.org. :wink:

#5

good morning @Lupp, :slight_smile:
everythink ok,
regarding ROUND() and filing a bug: will do that, just waited a little how the discussion here evolves,
regarding reporting and fixing: based on my experience it also takes ‘quite a while’ after the error was reported, so i was in no hurry, (and i’m in doubt if this problem will get high prio),
concerning mathematically correct results: the remark was not restricted to this special question but said to the accuracy of calc ‘in general’, and i hope you agree that ‘it is quite good, but could be improved here and there’?
concerning the capital letters: they are just a ‘habit’ that is e.g. handled differently in english than in german, imho they contribute little to the content, and i am ashamed to want to ‘make me big’ somehow with ‘I’ instead of ‘i’ …
regards,
b.

#6

@Lupp:
reg. ‘accuracy’ and violating basic mathematical rules:
from playing around with your sample sheet and with the ‘round_lupp’ function … there are changes, and it looks as if:

h = pNumber * 10^30
h = pNumber / 10^-30

now produces different result, and thus the rounding of your function ‘away from zero’ fails for e.g. -6,0835E-027
bug?
tested with 7.1 from 2020-09-05, was better in 6.1.6.3,
reg.
b.
‘wherever i lupp … new bug’ :wink:

#7

You have a misconception about the real number of digits in your number.

A floating point number is encoded with IEEE-754 format which, in the double case, allows for 53 bits for the mantissa. This provides at most ~17 significant decimal digits.

The matter is complicated by the fact that the base is 1/2 (not 2) and to get a human representation you convert between fractional bases 1/2 and 1/10 which cannot be converted exactly due to the 1/5 factor (2 and 5 are relative primes), leading to infinite periodic decimal expansion.

Thus you have not 32 decimal digits in your number but the string for it contains an infinite number of decimal digits for a 1/2 based fractions. Of course, only the few first are significant.

Your problem may then be to reduce the number of bits in the IEEE-754 encoding. This may make sense if the number is the result of calculation and the least bits are stained with errors (truncation or rounding). However, I warn you that altering the bits introduces further computation errors.

Before IEEE-754, this could easily be done by adding a non-normalised zero to the number. But IEEE-754 has specific rules about non-normalised numbers and the trick can no longer be used.

Trying to round off floating point numbers to a specific number of decimal digits makes no sense. Whatever you do cannot remove the primality between 2 and 5 and you’ll ever have an infinite expansion when converting from one base to the other. Your problem is a display one if any and must be handled accordingly.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.

#8

@ajlittoz: You are right concerning the internal limitations, of course.
However: The questioner tried to round to a decimal order of magnitude actually represented in the IEEE 754 representation of the example number. Am I on error?

#9

@Lupp: i would be quite happy about a critical review of tdf#40528, tdf#128312, tdf#133840 :slight_smile:
@all: i’m aware of the problem that the fp-representation messes up the decimal numbers we deal with on the sheet,
the attempt is to look if “len(number mod 1)” results in the decimals used in the UI rather than those used ‘internally’,
and from that on to find a reasonable! rounding, which does not have to be arbitrarily defined by the developers, but corresponds to the expectations of the user based on the entered / visible values, and common math rules,
some of it works, i stumbled over the boundary conditions ‘scientific string’, and over rounding to more than 20 decimal places as calc processes much smaller values and i would like to (try to) correct rounding errors of 0.1E-33 + 0.2E-33 as well,
the whole thing is not a solution for the real application, just an impulse for the developers that better results are! possible,
and on that way … whereever i lug … new bug …

#10

which does not have to be arbitrarily defined by the developers,

There is no rounding by developers. They don’t do any rounding and they most probably never will implement any rounding because the performance cost for a spreadsheet application will be too high. Spreadsheet mathematics fully relies on the machine/processor arithmetic of a 64-bit processors.

And @Lupp’s conclusion in his answer about your issue being a bug is purely formal, since the standard does not define an upper limit for digit and therefore ROUND() should accept any digit, even if it is far beyond of anything making sense for the current processor’s arithmetic (That’s how I understood the conclusion)

#11

Concerning the very old bug: I also stumbled over related problems when I once tried to sort by formulas. In an example I specifically had tuned, there were 138 out of 1000 numbers that were judged by COUNTIF() to be greater than themselves. Insofar the situation has improved. A recent test with the old example didn’t show a single case of the kind. However: Programming cannot resolve the underlying problem finally as long as not either
-Users edit and read numbers (comunicate with the software) in IEEE 754 Double (or any similar rep.).
-Spreadsheet software returns to BCD arithmetics ignoring all the modern hardware features.
-Processors shift to decimal.
In addition there is the fact that most numbers neither have an exact ordinary decimal nor an exact dyadic representation.
There cannot be exact involution between finite decimal and dyadic representations of numbers!
Attached to my answer by editing you will find an example exemplifying this to more detail.

#12

Quoting @anon73440385: “There is no rounding by developers. …”
I cannot find a plausible understnding of this. In addition there is a setting (I always strongly dissuade from enabling) named Precision as shown which in simple cases (no time, no fractional formats) actually seems to do as the name claims.
Quoting @anon73440385: “And _Lupp’s conclusion in his answer being a bug is purely formal, …”
There always is an implicit limitation like ...if not impossible due to logical or hardware limitations... This does not apply to the given case. Even =ROUND(1;-400) should clearly return 0 e.g. according to the specification.
For evidence see the above mentioned attachment, e.g.

#13

@anon73440385: i’m quite sure i’d read plenty comments about rounding in bugs, and discussions which level to use, might be it’s only used for UI display?

#14

@Lupp - if there was a rounding implemented in Calc then all discussions about about “Why is 0.043 - 0.042 = 0.000999999999999994 and not 0.001” would not occur and in that sense I made the statement. If there would be some (heuristic) rounding the value shown would be 0.001 (expected by many people excluding me).

Regarding your second quote: Isn’t that what I tried to express? It’s formal and it doesn’t refer to processor arithmetic and machine epsilon.

#15

@anon73440385: reg. your statement ‘0.043 - 0.042 = 0.000999999999999994 and not 0.001" would not occur’
imho it shouldn’t occur!!,
off from the question who’s right with his assumptions about the basics of calc’s behaviour …
important is the question:
!!! why should / do we nag plenty people with results neither fullfilling their expectations nor simple school math? !!!
first question ‘do we want that?’ - no,
second question ‘do we have to?’ - different opinions,
third question ‘if yes why?’ - different opinions,
‘unavoidable’? - no, i’ll attach a sample to my answer, see blue cells there, calc can! produce better results,
‘performance’? - yes!, you can’t handle big projects like that,
but!

  • most sheets are ‘small’,
  • there are plenty other and faster possibilities to implement even better decimal math,
  • hardware is improving,
  • for plenty tasks precision is more important than speed,
  • it’s a pity how much time is lost in discussions and for irritated users …
#16

@anon73440385
There was plenty of discussion, and there are misled expectations by the zillions.
The one way Calc tries to get rid of useless criticism was to create the mentioned option - and I also cannot propose another one. (I only dissuade from this one, and tell users to apply explicit rounding where rounding is wanted.) The fundamental limitations of “machine arithmetic” are mathematically inavoidable - given a fix length of RAM to be used for a number.
Concerning our different usage of the term “formal” I insist on mine. The mentioned bug actually is a (substantial) bug, and my mentioned advice to use explicit rounding relies -in principle- on the availabilty of correct (bug-free) rounding functions. Fortunately next to nobody will need orders of magnitude outside [-20…20].
We should discuss the rest sitting together with a glass of beer (each). After all, what do I have a motorbike for?

#17

Last comment: The main reason for all these types of discussions seems to be an unclear mixture of expectations, opinions on how it should be and how it is. Finally I accept the invitation for a beer :slight_smile:

#18

@anon73440385:

There is no rounding by developers. They don’t do any rounding and they most probably never will implement any rounding

That’s not true. You will notice for example that the calculation

=0.3-0.2-0.1

indeed produces 0.0 which it wouldn’t without some rounding, compare with

=RAWSUBTRACT(0.3;0.2;0.1)
#19

@erAck - ok,obviously I misunderstood everything @mikekaganski ever told about what developers do and wrote into bug reports on these issue(s). You know the code and I’m quiet now.

#20

@ajlittoz: sorry, sometimes i’m slow in thinking … :frowning:
‘You have a misconception about the real number of digits in your number.’
no - i have a quite good knowledge about ‘my number’, what’s irritating is what ‘fp-machines’ and calc make of it. i accept that it’s neccessary, and know something about how and why, but i’d really like better results and less questions,
and to play in that direction i needed the rounding of small values, and - surprised myself - it works, as well the rounding (not perfect in that sheet) as the correction of fp-artefacts, as of now better than expected … see sheet attached to my answer for @anon73440385 …

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