[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 User er changed the following: What|Old value |New value Status|UNCONFIRMED |RESOLVED Resolution| |DUPLICATE --- Additional comments from [EMAIL PROTECTED] Thu Sep 22 07:44:11 -0700 2005 --- Received the document from the submitter. Basically this boils down to a duplicate of issue 46681: in Excel, the one-dimensional result vector of $E$8:$E$217=Z$9 is repeated as many times as it is needed to multiply it with the two-dimensional result array of $A$8:$W$217=$Y15. In Calc, the vector is not repeated, therefore the remaining columns are all 0 and the result of the multiplication is a vector of 0s too. To achieve identical results in Excel and Calc, the formula could be rewritten as =SUMPRODUCT(($F$8:$F$217=$Y14)*($E$8:$E$217=Z$9)) note the now one-dimensional array $F$8:$F$217 of the column that is of interest here. Doing so would also increase speed of calculation since only the relevant column would have to be evaluated instead of 23 columns of which 22 are not needed for the result. Eike *** This issue has been marked as a duplicate of 46681 *** - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 User er changed the following: What|Old value |New value Status|RESOLVED |CLOSED --- Additional comments from [EMAIL PROTECTED] Thu Sep 22 07:44:36 -0700 2005 --- Closing dup. - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 User rainerbielefeld changed the following: What|Old value |New value Keywords|needmoreinfo, oooqa |oooqa OS/Version|Windows XP|All Platform|HP|All - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 User rainerbielefeld changed the following: What|Old value |New value Keywords|needmoreinfo, oooqa |oooqa --- Additional comments from [EMAIL PROTECTED] Wed Sep 21 01:58:08 -0700 2005 --- In a test document with confidential content (contributed by deanbond I found in Z12: =SUMMENPRODUKT(($A$8:$W$217=$Y12)*($E$8:$E$217=Z$9)) Someone with more knowledge shuld decide: That's a correct formula for EXCEL _and_ OOo? I do not get any errormessage, so it seems to be correct. To me it seems to be funny, that that formula has the result '210', if I use it in an empty calc document in 'Z12'. - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 User rainerbielefeld changed the following: What|Old value |New value Keywords|oooqa |needmoreinfo, oooqa --- Additional comments from [EMAIL PROTECTED] Wed Sep 21 02:02:28 -0700 2005 --- My test from comments from rainerbielefeld Wed Sep 21 01:58:08 -0700 2005 was with 2.0 (1.9.m125) German version WIN XP: [680m125(Build8947)] Opening the document with 1.1.4 (German) WIN XP: [645m52 (Build 8824)], I get an errormessage '#VALUE!' for that formula. @ deanbond: What formula do you read in 'Z12' using EXCEL? - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 --- Additional comments from [EMAIL PROTECTED] Wed Sep 21 04:22:13 -0700 2005 --- Hi Rainer, =SUMMENPRODUKT(($A$8:$W$217=$Y12)*($E$8:$E$217=Z$9)) Someone with more knowledge shuld decide: That's a correct formula for EXCEL _and_ OOo? Yes, it is. OOo1.x didn't support it in its plain form though, you had to enter it as a matrix/array formula. This changed with OOo1.9.x for Excel compatibility. To me it seems to be funny, that that formula has the result '210', if I use it in an empty calc document in 'Z12'. May look weird at the first impression, but is perfectly logical ;-) Internally, ($A$8:$W$217=$Y12) and ($E$8:$E$217=Z$9) create two arrays where each element is TRUE, the result of the comparison of an empty cell with an empty cell. The arrays are multiplied element by element, resulting in an array where each element has the value 1, and the elements are summed up then. As for the issue described by the submitter: without knowing the original data there's nothing we could say. I guess the 0 is because the comparisons produce results of FALSE, maybe because numbers are compared with strings, but this is just a guess. Eike - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 --- Additional comments from [EMAIL PROTECTED] Wed Sep 21 08:18:34 -0700 2005 --- Numbers are really numbers, no text, but: In my test document in Area 'A8:W217' many cells are empty E8:X217 seems to contain nothing, no numbers, no text, no blank. Can this cause the 0,00? If you don't think that that's the reason, I recommend to put the testfile to the disposal of [EMAIL PROTECTED] and Frank Stecher (Sun) for further evaluation; my knowledge here is very limited. @ deanbond Do you agree to mail your doc for confidential evaluation to Frank Stecher (Sun) and [EMAIL PROTECTED] - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 User deanbond changed the following: What|Old value |New value Attachment data| |Created an attachment | |(id=29739) Screen captures | |of Calc and Excel --- Additional comments from [EMAIL PROTECTED] Tue Sep 20 18:27:54 -0700 2005 --- Created an attachment (id=29739) Screen captures of Calc and Excel - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-issues] [Issue 54885] SUMPRODUCT calculates as zero
To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=54885 User rainerbielefeld changed the following: What|Old value |New value CC|''|'rainerbielefeld' Keywords| |needmoreinfo, oooqa Subcomponent|formatting|open-import --- Additional comments from [EMAIL PROTECTED] Tue Sep 20 22:44:55 -0700 2005 --- I see the effect in the screenshots, but currently I have no possibility to reproduce the bug. I did some quick tests with 2.0 (1.9.m125) German version WIN XP: [680m125(Build8947)] and saw SUMPRODUCT working as described in HELP. @ deanbond Pls. attach a small .xls that reproduces the problem - 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]