Apache OpenOffice (AOO) Bugzilla – Issue 59973
SUMPRODUCT function does not work as expected for different sized matrices
Last modified: 2006-02-01 14:31:03 UTC
I have a spreadsheet originally developed in Excel that uses the SUMPRODUCT function as follows: SUMPRODUCT((WEEK=$A43)* Duration) where WEEK is a matrix of names and Duration is the time spent on a task. The function should calculate the total time each person spends on their various tasks. This works as it should in Excel. In calc it produces erroneous results. The Excel spreadsheet is attached.
Created attachment 32852 [details] Excel spreadsheet showing correct result from SUMPRODUCT function
@vchapman, 1.0.2 is a little outdated ;-) so Priority to 5 But may be your problem still is actual in current versions? We have to discern to possibilities: 1. OOo function does not work in accordance with specification -------------------------------------------------------------- Please check whether results in your "Duty Schedule.xls" are in accordence with description in HELP. If not, please give detailled hints like "In cell X1 there should be a result "..." becuase Help says that .... ... .... OOo shows the result, it seems that ... ..., that's not ok because ..." Only so we can check that, we can't examine your comlete file and check whether something might go wrong. 2. OOo function "SUMPRODUCT " is ok, but import filter does not "translate" -------------------------------------------------------------------------- correctly --------- Please explain how the EXCEL-formula (please write down here in report) works and what results you see in EXCEL (screenshot!). Please explain in detail 1 of your EXCEL results in "Duty Schedule.xls" Thanks Rainer
I checked with "2.0.1 RC5 German version WIN XP: [680m1(Build8990)]", so I updated version. I will try to explain the problem some more clearly. 'Duty Schedule.xls' contains 2 named ranges which are relevant for this issue: - Duration (B3:B41) - WEEK (D3:H41) Pls. use the document navigator to verify! In C42 you find the formula '=SUMPRODUCT((WEEK=$A43)* Duration)' reporter expexts, that that will add all durations for Person "Almeida" In WEEK you find it - on tuesday 12:15 (20 minutes) - on thursday 12:15 (20 minutes) - on friday 12:15 (20 minutes) So, if the formula works as expected, the result should be 60, because: SUMPRIDUCT should multiply every '1' for WEEK=$A43 = Almeida with the duration in the same row and add all those results But the result is only "0" I believe, the reason is, that SUMPRODUCT only can work for matrices with same size, but the matrix "Duration" only has 1 column, "WEEK" 5 ones. That's the conflict. Normally for a function '=SUMPRODUCT(A1:B2;C1:D3)' you get an error message "#VALUE", because of the different sizes, but here in this application the different size of the matrices seems to be accepted without error message. But: because "Duration" only has 1 column, only the first column if matrix "WEEK" will be used for the calculation will be without any effect. In 'Duty Schedule.xls' you can check that, if you delete a Name in the first column of the yellow area "week", that will show an effect in the results, if you delete a name in an other column, that will be without any effect. I created a new spreadsheet 'Duty Schedule_working' with a 5 column matrix "Duration", that might satisfy reporter's needs. Because there the 2 matrices have the same number of columns, SUMPRODUCT works correctly and as expected. You can delete a name anywhere in the WEEK-area, and the effect will be that the affected time duration will no longer be added to the total time for the name. So we have to check: - Is that all in accordance to SUMPRODUCT specifications? - is SUMPRIDUCT the correct way how to calculate that with OOo, or is there another way that must be used - should we have a warning because of the different matrix sizes. - How can we improve our EXCEL import filter? May be we need further issues for all those questions. After checking,it should be decided whether Component should be "code" or "import" or something else. @vchapman: did my obervations meet your problem?
Created attachment 32903 [details] Thes new spreadsheet might satisfy reporter's needs
My mistake, I am using oo ver. 2.0.1.
I am afraid that I am reaching the limit of my technical competentacy to discuss this issue. However, I attach my preferred workaround. I have changed SUMPRODUCT((WEEK=$A43)* Duration) to =SUMPRODUCT((MONDAY=$A43)*Duration)+SUMPRODUCT((TUESDAY=$A43)*Duration)+SUMPRODUCT((WEDNESDAY=$A43)*Duration)+SUMPRODUCT((THURSDAY=$A43)*Duration)+SUMPRODUCT((FRIDAY=$A43)*Duration)
Hi, this is a double to Issue 46681. Frank *** This issue has been marked as a duplicate of 46681 ***
closed double