[Libreoffice-bugs] [Bug 140691] calc: calculation: sequence of operations neglected in many tasks, only mathematical better results should be allowed if calc differs from ex$el | was: sum of range: (and similar) - no compatibility with ex$el reg. ordering of operands

Wed Apr 21 11:23:33 UTC 2021

https://bugs.documentfoundation.org/show_bug.cgi?id=140691

--- Comment #9 from b. <newbie-02 at gmx.de> ---
Created attachment 171329
  --> https://bugs.documentfoundation.org/attachment.cgi?id=171329&action=edit
a_file_with_some_calc_only_processing_sequence_problems

it's not only ex$el compatibility, but already calc in itself having problems
with different processing order, see e.g. the statusbar deviations, and
attached sample, 

the red marked results are different than one would expect, 'sum' and
'sumproduct' calculate in different order, without that the pairs of results
should at least be identic, 

i put that first as it might point into the right direction: 

i found something about sequences in the OASIS doc:
https://docs.oasis-open.org/office/OpenDocument/v1.3/cs02/part4-formula/OpenDocument-v1.3-cs02-part4-formula.html#Operators
: 

"4.11.12 Sequences (NumberSequence, NumberSequenceList, DateSequence,
LogicalSequence, and ComplexSequence)

Inside a sheet, it is implementation-defined as to whether the values are
processed row-at-a-time or column-at-a-time, but it shall be one of these two
processing orders. If processing row-at-a-time, the sequence shall be produced
by processing each row in turn, from smallest to largest column value (e.g.,
A1, B1, C1). If processing column-at-a-time, the sequence shall be produced by
processing each column at a time, from the smallest to the largest row value
(e.g., A1, A2, A3)." 

can't say if that is applicable here or might only be used as a hint that
'processing sequence matters' and 'there are ideas about guidelines', i ask
that the more experienced developers check that. 

if that applies it's meaningful, but should - imho - be refined as it matters
if you calculate a range row by row or column by column, results may be
different. if one cannot agree to a standard give it to the user, make
processing: 
*(A1:C3) -> A1, B1, C1, A2, B2, C2, A3, B3, C3,* 
(C1:A3) -> C1, B1, A1, C2, B2, A2, C3, B3, A3,
(A3:C1) -> A3, B3, C3, A2, B2, C2, A1, B1, C1,
(C3:A1) -> C3, B3, A3, C2, B2, A2, C1, B1, A1,
*(1A:3C) -> A1, A2, A3, B1, B2, B3, C1, C2, C3,* 
(3A:1C) -> A3, A2, A1, B3, B2, B1, C3, C2, C1, 
(1C:3A) -> C1, C2, C3, B1, B2, B3, A1, A2, A3, 
(3C:1A) -> C3, C2, C1, B3, B2, B1, A3, A2, A1, 

(In reply to Mike Kaganski from comment #8)
> (In reply to b. from comment #7)
> > as far as it's a matter of 'standards' it's a clear weakness of them, and
...
> > - yeah, and it's time to get that noticed and be aware of, 
> 
> I don't see what you are trying to tell with this "it's time to get that
> noticed and be aware of". You can't be aware of the undocumented data layout
> in a closed-source program like Excel. You can't rely on it even if you
> somehow made assumptions and reverse-engineered that.

it's three things: 
1. be aware of calculation sequence issues, 
2. if we try to position LO calc as a good substitute for ex$el, there are a
couple of requirements, 
3. aiming compatibility to *.xls and *.xlsx file format relies on reverse
engineered assumptions and is subject to power to change by M$ as well, 

> result in 15th significant decimal, if that is a justified change.
- regarding operation sequence / operand ordering besides mostly only injecting
small irritations we are talking about the risk of much bigger differences up
to total fail, 

> E.g., a purpose to allow implementers to use different, unknown in advance,
> optimization strategies (like parallel access). Spreadsheets are
> well-espablished, and didn't appear yesterday. They have international
> standards with several revisions, each approved by international bodies of
> experts. They all know what order of evaluation is (such order is always
> defined e.g. in most programming languages, and people there in TCs are
> perfectly aware of pros and contras of strict specification of that). It's
> just not reasonable to imagine that omission of this spec in all published 
> standards is "shortcoming" or "omission"; it was mentioned in many places
> like [1], and is not a well-hidden secret.

will read [1] in detail, on a first glance i'd say they are not a normative
committe and talking with too simple scope not taking round-off and
cancellation into account ... 

> It's a problem of *standard*. If you assume that e.g. XLS(X) formulas need
> to have some *specific* order of evaluation, then it would be normal that MS
> defined it; but then the ODS spreadsheets are a separate thing, and they
> have features not present in XLSX - so following the train of thought, the
> order of evaluation of arguments in *that* file format would be something
> that *its* author (OOO and later LO) defined, so - following it further - MS
> Excel should now start to evaluate formulas differently when it opens ODS.

imho - but am at the beginning to think about - it's less a point of the file
or file format, but of the interpretation of arguments to a function by the
program processing the function, i also thought about defining file format
related processing sequences, think that would make things worse than better
??? 

> > > and suggestions that allow to improve calculations. 
> > - i hope you won't start citing Adolf Hitler? who once spouted such nonsense
> > as: 'Only the one who can solve a task better is entitled to criticism.' 
> > no, we all make better progress if even the people who can not yet eliminate
> > errors are allowed to point them out. 
> 
> You are attacking personally, 
don't be so / too sensitive, after i've been called an idiot i'm allowed to
express myself concretely, 

and yes, you are right, no, you did not quote A.H., but 'we accept only this
and that' is close to his egocentric way, that's why I warned to repeat errors
of him before! it happens. 

> make LO follow unspecified order of evaluation of a closed-source
> application" is not.
that's M.K. making the same fault he just critizied at others, i never said
that, i said i know the - actual - M$ processing sequence and know that it's
different to calc, know that it's injecting problems and propose to discuss
that, it's not my fault that calc is a excel clone in many respects, i didn't
design or program it, i pinpoint some weaknesses, and that's as well that the
processing order is problematic for compatibility, as well as it's a problem in
calc itself, as well that it's obviously neglected by the programmers and devs
and you! so far.  

and instead of meaningful discussions and bringing in your knowledge to benefit
the cause, you make stupid statements, and then can't take the answers

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210421/70f919d4/attachment-0001.htm>