Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||DCOUNT(..;0;..) counts text cells|
|Component:||programming||Assignee:||AOO issues mailing list <issues>|
|Status:||CONFIRMED ---||QA Contact:|
|Priority:||P3||CC:||issues, kpalagin, rb.henschel|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
Description drking 2007-12-02 13:10:57 UTC
DCOUNT should only count cells with numbers, same as COUNT A B 1 pet name 2 dog fido 3 cat meow 4 dog rover 5 6 pet name 7 dog DCOUNT(A1:B4;0;A6:B7) returns 1, with not a number in sight although DCOUNT(A1:B4;2;A6:B7) returns 0, correctly The use of 0 here is poorly explained in the Help (I'm re-writing it currently), and it is not compatible with Excel, at least with older versions. But in any event it should only count numbers.
Comment 1 kpalagin 2008-01-10 11:58:45 UTC
Confirming with 2.4m241 on WinXP - if second parameter (database_field) is zero for DCOUNT, we apparently count non-digits. Excel2003 in such case produces #VALUE.
Comment 2 drking 2008-01-10 12:55:08 UTC
Excel2003 probably produces #VALUE because it doesn't understand 0 as a parameter? When someone looks at this, it would be useful to know exactly what field=0 is *supposed* to do.. does it count any row which has a number in any column, or any cell containing a number, or ... many thanks
Comment 3 Regina Henschel 2009-08-02 00:24:30 UTC
The index 0 is an old syntax from StarOffice 5.2 and has the same meaning as using the whole data table range as second parameter. In this cases StarOffice 5.2 applies the aggregate function to the right most column of the data table for DCOUNT and for DCOUNTA. In StarOffice 5.2 you will get the correct result 0 for your example. The results in OOo2 and OOo3 for the value 0 for the "Field" parameter differ from those in StarOffice 5.2 and are wrong, not only for the new file format but also when opening an old sdc file. I have no version OOo 1 installed to look there. The ODF 1.2 draft spec does not mention this syntax at all, but only uses the singular "Field". In addition I find "A field selector that selects a non-existent field is an error." in chapter 4.9.7. This would be true for the value 0, because the leftmost column has index 1. Therefore I think, that OOo should return an error for the old syntax, when it is used in an ODF format and use the old implementation of "apply aggregate function to rightmost column" with the correct meaning of DCOUNT and DCOUNTA concerning number and text, when it is used in an old, non ODF format. The help is very unclear in this point and needs to be improved, not only concerning value 0 of the second parameter. For example the text "Indicates which database field (column) is to be used for the search criteria." for the second parameter is nonsense. The second parameter determines the field on which the aggregate function is applied. Which fields are used for the search criteria is part of the search criteria range itself.
Comment 4 drking 2009-08-02 07:04:18 UTC
Thanks, Regina. I wrote some improved help on the Wiki back in 07: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_DCOUNT_function It links back to this issue.