https://bugs.documentfoundation.org/show_bug.cgi?id=153422
David Alden <dav5al...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Ever confirmed|1 |0
Status|NEEDINFO |UNCONFIRMED
--- Comment #6 from David Alden <dav5al...@gmail.com> ---
Dear Libre Office Writer QA Administrators,
Thanks for following up with this bug report. I'm sorry the explanatory
material included in the attachment was not sufficiently clear. The attachment
provided (TableDemo1.odt) was derived from an attempt to use the capability of
LO Writer to compute formulas according to US Federal and State income tax
worksheets within tables and fields in an ODF text document. The income tax
worksheets often require conditional computation of values dependent on other
entered or computed values. In an attempt to use the LO Writer table formula
capabilities for this purpose, anomalous results were observed from properly
constructed formulas in all currently available versions of LO. Rows 4-9 in
Table1 of TableDemo1.odt provide examples of both correct and anomalous formula
evaluations each with a brief note <A1:9> as to what the <C1:9> value or
formula is intended to demonstrate (<C1> is editable but <C2:3> are fixed).
The Table1 formulas in <C4> and <C5> evaluate correctly for all values entered
in <C1>, specifically the integers -2, -1, 0, 1, or 2 for demonstration
purposes. These are the simple <C4> formula: "=<C3>+<C1>*<C2>" and the
conditional <C5> formula "=max((<C1>*<C2>+<C3>)*(<C1>g 0)) | (<C3>*(<C1>leq
0)*3/4)" as shown in the rightmost column at the bottom of the attachment
(TableDemo1.odt) [these active formulas can be verified by hovering the pointer
over the appropriate cells in Table1 (<C4> or <C5>)]. The simple <C4> formula
is provided just to demonstrate that common algebraic logic (e.g.,
multiplication has computational precedence over addition) is correctly
implemented without the need for parentheses. The conditional <C5> formula is
provided to demonstrate that such conditional computation can evaluate
correctly if exhaustive parentheses (i.e., none are left out) are included in
the formula. The <C5> formula evaluates correctly to either the same value as
<C4> or <C3>*3/4 (4258.5) depending on whether cell <C1> is greater than (g)
zero or less than or equal to (leq) zero respectively. They are shown in the
rightmost column of lines marked 4. and 5. at the bottom of the attachment
document along with the correct expected values (columns 2-6) these formulas
produce for the above mentioned values of <C1> without any highlighted (i.e.,
no anomalies). So far so good. However, the exhaustive parentheses should not
be necessary to correctly evaluate the formula in <C5> and when they are
removed, anomalous evaluations of the formula <C6> and <C7> are observed
(expected results are highlighted in yellow at the bottom of the document in
the row labeled 6.). Note that the "(<C1>g 0)" condition has also been removed
for the <C6> and <C7> formulas along with most of the parentheses included in
the <C5> formula to simplify the expression to "=max <C3>+<C1>*<C2> |
<C3>*(<C1>leq 0)*3/4". For the two negative values of <C1> and zero, the left
component of the max function will be either 3210, 4444 or 5678 respectively
compared to 4258.5 on the right of the pipe (element separator). But the <C6>
formula actually evaluates to 9936.5 instead for all three (note that this
value shouldn't even occur at all). For the two positive values of <C1>, the
right hand component of the max function should be zero so only the left hand
component should matter with values 6912 and 8946 respectively but the function
actually evaluates to 5678 instead. The <C7> function simply reverses the
order of the left hand arithmetic expression, <C1>*<C2>+<C3> instead of
<C3>+<C1>*<C2>, and the results are even stranger: 1790.5, 3024.5, 4258.5, 1234
and 2468 instead of the expected 4258.5, 4444, 5678, 6912 and 8946 for <C1> -2,
-1, 0, 1 and 2 respectively. {At this point, please accept my apologies for
the confusion resulting from my copy/paste and editing failure error caused by
playing around with the <C6> and <C7> formulas and neglecting to fix the
results that originally included the (<C1>g 0) condition as a part of the left
max component after having removed it from both <C6> and <C7> formulas. I have
fixed the expected values in the corresponding lines in the replacement
TableDemo1.odt attached below.} In other words, even without parentheses, the
arithmetic, operator and/or function calculations between the list separators
(pipe characters) should be completed first regardless of how many there are
before evaluating the max function or other functions that take lists (all the
LO Writer "statistical functions", e.g. "=product (min 1|2|3) | (mean 2|4) |
(max 1|3|5|7)" should compute 1*3*7=21 but produces 8 instead--weird 😕
[generally, the parentheses would usually be necessary to indicate which list
separator goes with which function when the formula contains multiple separate
lists]). This reasonable expectation is apparently not what the LO Writer
function parser is doing and I haven't been able to tell from various results
what actually is happening. The last two formulas <C8> and <C9> demonstrate
that sometimes list components appear to be completely ignored by the parser.
The evaluation of <C8> "=max 1550 | (min 2350 | .45*<C4>) | 1111" always
evaluates to 1111 but changing the order of these last two list components as
in <C9> "=max 1550 | 1111 | (min 2350 |.45*<C4>)" helps only with the two
negative values of <C1>, formula <C9> evaluates to 2555.1, 3110.4 and 3665.7
for <C1> 0, 1 or 2 respectively instead of the correct 2350 value shown in the
last two lines at the bottom of the attachment TableDemo1.odt. The only part
of the LO Writer table function bug report that remains to be mentioned is the
failure of the recompute/update routine to interpret a null or blank cell as
zero and reevaluate all the formulas accordingly (it appears to retain the last
numeric value even after it has been blanked or nulled out).
I sincerely hope this much more detailed description of the LO Writer
formula evaluation anomalies helps to clarify any remaining questions you had
regarding the indicated bug report. Again, sorry for the confusion resulting
from the erroneous values left in lines numbered 6. and 7. at the bottom of the
demonstration attachment. Please find a corrected version below. I will also
use the bugzilla interface to update the file.
Appreciatively,
Dave Alden
--
You are receiving this mail because:
You are the assignee for the bug.
