Hi Yue, On Wednesday, 2008-03-05 11:00:37 +0800, Lvyue wrote:
> I have made a little change on issue86643. > now COUNT() can ignore errors, and give correct count of numbers. See also the comment I added to the issue. > but I found it ignores all errors. > for example, if the cell A1 has a formula =COUNT(A1), > the result will be 0. > and before I change, the result was Err:522. > Is that right? Good that you mentioned. Comparing with Excel this is indeed a strange case. Also there A1:=COUNT(A1) results in 0 (after having popped up a warning about circular reference), but A1:=COUNT(A1:A2) and the value 1 in A2 still results in 0, which looks broken to me. > I find many formulas will return Err:522 in this case, All circular references should result in that error, except for formulas where ISERROR() or ISNUMBER() or similar check for the type or evaluate the error. And now COUNT() and COUNTA() as well. > but for COUNT, I think it is ok. > however I don't know if this is the result you want. Error values are to be ignored, this is also what the ECMA/MOOXML spec says, so not counting the formula cell producing the circular reference error and not propagating the error is the logical consequence. Also =COUNT(1/0) as an error case example should ignore the error and not increment the count. To make that work a check for the stack type svError is needed in ScInterpreter::IterateParameters() and ignore that for the case of ifCOUNT, setting nGlobalError to 0. In ScInterpreter::Interpret() the OpCode ocCount needs to be added to the CASE_OCERRFUNC macro. > I also found formula =COUNTA(A1) returns 1, > not Err:522. Which fits well. Also =COUNTA(1/0) should return 1, and ocCount2 be added to the CASE_OCERRFUNC macro to make that work, and the check for svError in IterateParameters() counting the error for ifCOUNT2, again setting nGlobalError=0. It gets more complicated now though: for incorrect references, after having called PopSingleRef() or PopDoubleRef(), nGlobalError is set. To be consistent this must be checked as well, and the error ignored for ifCOUNT respectively counted for ifCOUNT2. A test case for that would be: - B1: 1 - A1: =COUNT(B1) => 1 - A2: =COUNTA(B1) => 1 - Delete column B => result A1==0, A2==1 Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to the [EMAIL PROTECTED] account, which I use for mailing lists only and don't read from outside Sun. Use [EMAIL PROTECTED] Thanks.
pgpgArHqSv9JK.pgp
Description: PGP signature
