Issue 118213 - matrix calculation error
Summary: matrix calculation error
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:
Depends on:
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: ---

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

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
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
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