Bug 37765 - Function COUNTA: Text entries no more counted when argument is a label
Summary: Function COUNTA: Text entries no more counted when argument is a label
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.0 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-05-30 12:40 UTC by pierre-yves samyn
Modified: 2014-11-18 08:51 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Attached spreadsheet already includes range and formula (8.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-05-30 12:40 UTC, pierre-yves samyn
Details
COUNTA_V2.ods (8.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-11-28 15:01 UTC, pierre-yves samyn
Details
COUNTA_V2modified.ods (9.49 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-14 07:14 UTC, Winfried Donkers (retired)
Details
Screenshot: file opened with OOo 3.2.1 (14.87 KB, image/png)
2014-11-16 08:22 UTC, pierre-yves samyn
Details

Note You need to log in before you can comment on or make changes to this bug.
Description pierre-yves samyn 2011-05-30 12:40:31 UTC
Created attachment 47338 [details]
Attached spreadsheet already includes range and formula

Hello

Build Date & Platform: XP LibO3.4RC2 buildid=340m1(Build:12) 

Steps to Reproduce:

- Create a new spreadsheet (reproduced on an existing one)
- Verify Tools> Options> Calc> Calculate> Automatically find column and row labels is checked
- In E5 enter text Instruments
- In E6 Bass
- In E7 Violin
- In E8 Viola
- In A1 =COUNTA('Instruments')

Expected Result: 4

Actual result: 0 (zero)

Regression : Help says "Counts how many values are in the list of arguments. Text entries are also counted, even when they contain an empty string of length 0"

cf. http://help.libreoffice.org/scalc/SC_HID_FUNC_ANZAHL2?Language=en-US&System=WIN&Version=3.4#COUNTA

Nota : =COUNTA(E5:E8) is OK

Best regards
Comment 1 Rainer Bielefeld Retired 2011-06-10 02:57:30 UTC
RC2 is bit by bit identical with release version, so separate items in the version picker are useless. Changes have been discussed with Michael Meeks.
Comment 2 Björn Michaelsen 2011-12-23 12:01:33 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 3 pierre-yves samyn 2011-12-24 02:27:22 UTC
Hello

Still persists with Windows 7 &LOdev 3.5.0beta2+ Build ID: 62b9587-7ef74e0-7bf1c81

Regards
PYS
Comment 4 Muthu Subramanian 2012-11-28 14:13:20 UTC
I guess its with the wrong use of parameters - it needs to be
=counta("Instruments") -> with the double quotes.

There is already a bug referring to single quote parameters.

Close this as worksforme or invalid?
Comment 5 pierre-yves samyn 2012-11-28 15:00:25 UTC
Hello

(In reply to comment #4)
> I guess its with the wrong use of parameters - it needs to be
> =counta("Instruments") -> with the double quotes.

Thank you for your interest, but I think you have not understood the problem.

The parameter does *not* need double quotes. The single quotes refer to a *label*.

As stated the problem is for text, it is ok for numbers (see A3 in the new attachment COUNTA_V2.ODS)

Thank you for not closing this bug which, incidentally, still occurs with the version  3.6.3.1 

Regards
Pierre-Yves
Comment 6 pierre-yves samyn 2012-11-28 15:01:07 UTC
Created attachment 70735 [details]
COUNTA_V2.ods
Comment 7 Winfried Donkers (retired) 2014-11-14 07:14:21 UTC
Created attachment 109448 [details]
COUNTA_V2modified.ods

I looked into the behaviour and have come to the conclusion that it is not a bug.
The output of COUNTA('Instruments') is 1, which is correct: only Europe follows.

I added more continents to the row in the attachment to show the effect.

Automatic label recognition can apply downwards of the label or to the right in the case of numeric values, but to the right only in the case of textual values.

As there is data below _and_to the right of label 'Instruments', it would be wise to assign a name to a range (Insert-Names-Define...), as done in the attachment.
Comment 8 Winfried Donkers (retired) 2014-11-14 07:17:38 UTC
Closing this bug report.

Pierre-yves, if you think that not automatically recognizing a range of textual values below a label is a bug, would you please enter a new bug report for this?
Comment 9 pierre-yves samyn 2014-11-16 08:21:50 UTC
Hi Winfried 

Thank you for watching this report.

(In reply to Winfried Donkers from comment #7)
> Automatic label recognition can apply downwards of the label or to the right
> in the case of numeric values, but to the right only in the case of textual
> values.

What you describe does not conform to the feature in the previous version.
It is indeed a regression as shown in the attached screenshot: your attachment opened with OOo 3.2.1

No one would understand the logic to count the numbers "vertically" and text "horizontally".

Best Regards
Pierre-Yves
Comment 10 pierre-yves samyn 2014-11-16 08:22:32 UTC
Created attachment 109546 [details]
Screenshot: file opened with OOo 3.2.1
Comment 11 Winfried Donkers (retired) 2014-11-16 14:44:09 UTC
(In reply to pierre-yves samyn from comment #10)
> Created attachment 109546 [details]
> Screenshot: file opened with OOo 3.2.1

Hi Pierre-Yves,

I may misunderstand you:
Re your screenshot (taken from OOO 3.2.1):
-result in cell A1 could apply to F5-H5 or E6-E8, both would produce 3. Adding something in I5 increases the result (in LO 4)
-result in cel A5 aplies clearly to the absence of cell(s) with content to the right of cell E11
-result in cell A7 may be 0 because the name a (Insert-Names-Manage...) was not defined to be cells C22-C24. I don't know if named ranges were supported in OOo3.2.

Apart from the result in cell A7, the results are identical in OOo3.2 and LO4, i.e. I see no regression.

Possibly the reason behind the difference between numerical and textual cells is that in this example:
      COLA COLB COLC
RONE     1    2    3
RTWO     4    5    6

COLA, COLB, RONE and RTWO are all recognized as labels, where COLA is recognized as a label for (1,4) and not for (COLB,COLC) and RONE is recognized as a label for (1,2,3) and not for RTWO.
I don't know for certain and will check this in the code; at least numeric ranges have preference over textual ranges.

Please tell me if I do misunderstand you and give me some time to check the code.
Comment 12 Winfried Donkers (retired) 2014-11-16 14:46:46 UTC
Because nothing had been fixed, setting the status back to NEW, at least as long as there is discussion.

I removed regression, though, as I don't see a (LibreOffice) version where the behaviour was as expected.
Comment 13 pierre-yves samyn 2014-11-17 08:29:57 UTC
Hi Winfried

You're absolutely right. I redid the test with OOo 3.2.1 and the results are the same as with LibreOffice. This is not a regression.

So I feel very stupid... :(

This bug report is old now. I guess that's the difference text/number that misled me. The difference is not intuitive, obvious, when the table has only one column.

Anyway, request a change should be subject to a new report.

I close this bug and thank you again for your investment.

Best regards
Pierre-Yves
Comment 14 Winfried Donkers (retired) 2014-11-17 08:59:34 UTC
(In reply to pierre-yves samyn from comment #13)

No need to feel stupid, if I hadn't uploaded an ambivalent example, the misunderstanding would not have taken place.

Anyway, the code to automatically recognize labels is very interesting (and complex) and I might be able to see why (when no numeric range can be found) calc only searches for horizontal ranges. Then I may be able to fix this. In that case I will create a bug report and include you in the CC.
Should you wish to enter an enhancement request, please add me in the CC.

Feedback from users and exchange of experiences/ideas lead to a better LibreOffice, we all contribute in our own way :-)