To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=59973
User rainerbielefeld changed the following:
What |Old value |New value
================================================================================
Ever confirmed| |1
--------------------------------------------------------------------------------
Status|UNCONFIRMED |NEW
--------------------------------------------------------------------------------
OS/Version|Windows 2000 |Windows, all
--------------------------------------------------------------------------------
Priority|P5 |P3
--------------------------------------------------------------------------------
Version|1.0.2 |OOo 2.0.1
--------------------------------------------------------------------------------
------- Additional comments from [EMAIL PROTECTED] Wed Jan 4 08:39:09 -0800
2006 -------
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?
---------------------------------------------------------------------
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]
