Issue 118213 - matrix calculation error
Summary: matrix calculation error
Status: CLOSED IRREPRODUCIBLE
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 3.2
Hardware: PC All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-06-25 15:45 UTC by risch
Modified: 2017-05-20 09:57 UTC (History)
5 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
L3:L34 should bring identic results to K3:K34 (18.84 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-06-25 15:45 UTC, risch
no flags Details
Simplified example (21.43 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-06-25 21:41 UTC, risch
no flags Details
further simplified example (15.91 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-02-03 09:52 UTC, risch
no flags Details
matrix formulas with encapsulated matrix and plain formulas (12.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-01-14 14:17 UTC, Prosper Uniger
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description risch 2011-06-25 15:45:51 UTC
Created attachment 76635 [details]
L3:L34 should bring identic results to K3:K34

Matrix formula does not always bring expected result when using certain formulas. There seems to be a kind of internal overflow error caused by the useless evaluation of erroneous expressions.
Comment 1 Regina Henschel 2011-06-25 17:04:12 UTC
There seems to be something wrong in the attached spreadsheet. I get no values in column K and L, and E as well, and errors in column J.

It is not clear to me, what you try to calculate and why you need the formulas in array-version. Please try to provide a spreadsheet, where you explain your formulas. The German labels are not helpful for an international audience.
Comment 2 risch 2011-06-25 21:41:01 UTC
Created attachment 76637 [details]
Simplified example

for a row of payments, I try to calculate a period index and the corresponding date - depending on parameters (start date, duration, payment terms and due date).
1) the periode calculates well with a matrix formula in E3:E245
2) a normal formula calculates the date in F3:F245
3) I try to do the same with a matrix formula in G3:G245
   -> G3 and G35:G245 are calculated wrong
4) Tracking down the issue I simplify the normal formula in H3:H245
5) Now I do the same with a matrix formula in I3:I245
   -> the inner "IF" seems to override the rest of the formula
6) Nor I replace the inner "IF" with a "..." string in K3:K245
   -> it works, maybe only two IFs can be capsulated in a matrix formula
7) Doing the same with a normal formula in I3:I245
   -> instead of "..." it shows 0
Comment 3 risch 2011-06-26 06:57:28 UTC
7) error was usage of single instead of double quotes...
however, the matrix formula issues remain

I know array formulas are rather a question of taste - in this case I could use normal formulas, too. However, I prefer arrays to mark, which cells use the same formula and thus make calculations in the sheet more transparent and easier to maintain.
Comment 4 Oliver Brinzing 2011-06-27 13:18:05 UTC
.
Comment 5 Oliver-Rainer Wittmann 2012-06-13 12:18:02 UTC
getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.
Comment 6 Rob Weir 2013-02-02 02:57:58 UTC
This Issue requires more information ('needmoreinfo'), but has not been updated
within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. 

You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download

Please report back the outcome of your testing, so this Issue may be closed or
progressed as necessary - otherwise the issue may be Resolved as Invalid in the
future.
Comment 7 risch 2013-02-03 09:52:48 UTC
Created attachment 80201 [details]
further simplified example

I tested with OOo v3.4 but the issue remains:
When capsulating more than two "IF" clauses in a matrix formula, the outer "IF" clauses are not interpreted.

Task: The rox should return empty if the corresponding E3:E425 row is empty.

Example with two capsulated IF-clauses:
G3:G425 (matrix) behaves like F3:F425 (non-matrix).
-> no problem

Example with three capsulated IF-clauses:
I3:I425 (matrix) assumes all IF-clauses to be false and always returns the false part of the inner IF clause
-> this is a matrix bug as if does not happen with normal formula (H3:H425) or in a matrix with only two capsulated IF claused (G3:G425)
Comment 8 Edwin Sharp 2014-01-13 18:14:15 UTC
Can not confirm comment 7

AOO410m1(Build:9750)  -  Rev. 1555399
2014-01-06_04:13:46 - Rev. 1555657
Debian
Comment 9 Prosper Uniger 2014-01-14 14:17:12 UTC
Created attachment 82278 [details]
matrix formulas with encapsulated matrix and plain formulas

I recreated the test case from risch with simple data

the structure of the matrix formula in column F has the same structure as risch's test case
there is an underlying problem which is shown in column D

the whole case is a bit dubious because there is a mix of matrix and plain formulas

{ =IF(matrixA=X; A; B) } works - column B
{ =IF(valueA=X; A; B) } works - column C
{ =IF(matrixA=X; A; IF(matrixB=Y; B; C)) } works - column E

{ =IF(matrixA=X; A; IF(valueA=Y; B; C)) }  doesn't work - column D - value depends on the result of the last comparison in matrixA (try changing A12 to 2 or 3)

column F is a mix of both cases with a whole different result

maybe warn users if they mix matrix with plain formulas might be a good idea because this logical error is easily overlooked