To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=105494 Issue #|105494 Summary|A formula with long arrays may fail with error #VALUE! Component|Spreadsheet Version|OOo 3.1.1 Platform|PC URL| OS/Version|Windows XP Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P3 Subcomponent|programming Assigned to|spreadsheet Reported by|goodsteinp
------- Additional comments from goodste...@openoffice.org Thu Oct 1 01:12:17 +0000 2009 ------- The attached spreadsheet extract "Bug Report - Failure.ods" reports the calculation error in Column D when an entry in Column A is present. The formula for Column D (row 6 to row 500) is: IF(A6:A500>0;IF(A6:A500<>B6:B500;C2*(A6:A500-1)+1;C6:C500);C6:C500) and the error appears in cell D135. I had prepared the full spreadsheet on which this extract is based by reference to a similar spreadsheet with arrays of 1000 rather than 500. The attached "Bug Report - Successful.ods" is an extract of that spreadsheet with apparently identical data and formulas, except for the greater array length. The formula for Column D (row 6 to row 1000) is: IF(A6:A1000>0;IF(A6:A1000<>B6:B1000;C2*(A6:A1000-1)+1;C6:C1000);C6:C1000) which is indeed identical to the other formula subject to substituting 1000 for 500 throughout. However, this spreadsheet correctly calculates cell D135. It is noteworthy that if, in the failure sheet, a new column is created adjacent to Column A and is to be populated from row 6 to row 500 by multiplying Column A if positive by a constant, then we will have a failure in row 135. If we create more values in Column A, then we will get a failure in each such row. No such failures appear in the success sheet, working from row 6 to row 1000. The workround here appears to be to substitute arrays from row 6 to row 1000 throughout. Why this should be is not clear. I believe that this issue is symptomatic of a deep seated bug in the calculation of column formulas involving array processing, since I have noticed (but not reported) similar problems before. It will of course be necessary to check that there is not some hidden difference in the two versions of Column A. --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@sc.openoffice.org For additional commands, e-mail: issues-h...@sc.openoffice.org --------------------------------------------------------------------- To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org