To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=57401
User atdsm changed the following:
What |Old value |New value
================================================================================
Ever confirmed| |1
--------------------------------------------------------------------------------
Status|UNCONFIRMED |NEW
--------------------------------------------------------------------------------
Keywords| |oooqa
--------------------------------------------------------------------------------
Summary|Array formula |COUNTIF in Array Formula
|incompatibility with Excel|counts 0's (zeros) when
| |using non-numeric
| |characters
--------------------------------------------------------------------------------
------- Additional comments from [EMAIL PROTECTED] Mon Dec 5 06:23:21 -0800
2005 -------
Correction 1: In Excel 2003, entering {=COUNTIF(<range>,<range>)} into a cell is
treated as text. You must enter =COUNTIF(<range>,<range>), then press
CTRL+SHIFT+ENTER to have the formula entered as an array formula. Also in Excel,
in order to have the array formula spread over 5 cells (in other words, to
actually have it be an array) you must select all five cells first, then enter
the formula, then hit CTRL+SHIFT+ENTER
Correction 2: As far as I can tell, the array formula is supposed to span five
cells when you enter it. This is normal and proper behavior, for two reasons.
First, it is counterintuitive to have to select the cells THEN enter the array
formula, as Excel does it. Instead, OOo does the right thing: autodetects when a
formula is set up to be an array formula and fills it down accordingly. Which
brings me to reason number 2: OOo autodetects that you are using COUNTIF as an
array formula because you entered a range in the second argument to COUNTIF.
COUNTIF normally takes =COUNTIF(<range>,<criteria>), but you fed it
{=COUNTIF(<range>,<range>)}. Thus, it sees the array formula brackets and sees a
range instead of a criteria, and correctly autofills the entire 5 cell range
without you needing to do it manually. Thus, what Excel does is actually
improper IMO; it makes no sense to have an "array" formula in one cell only.
Nevertheless, I agree with you that there is a bug in the calculation of the
array formula when using non-numeric cell contents. Here's what I get when using
the formula {=COUNTIF(A1:A5;A1:A5)} for three different data sets:
A B
----------------
1 | Q 0
2 | R 0
3 | Q 0
4 | T 0
5 | U 0
A B
----------------
1 | 1 2
2 | 2 1
3 | 1 2
4 | 3 1
5 | 4 1
A B
----------------
1 | @ 0
2 | # 0
3 | $ 0
4 | % 0
5 | ^ 0
The formula only works properly with the numeric data set.
Confirming (Windows XP SP2, OOo 2.0)
Changing summary to reflect actually bug.
---------------------------------------------------------------------
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]
