[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: [OASIS Issue Tracker] Updated: (OFFICE-3764) Public Comment: Cannot resolve parameter type "Criterion", which is used in SUMIF e.g.
[ http://tools.oasis-open.org/issues/browse/OFFICE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Durusau updated OFFICE-3764:
------------------------------------
Fix Version/s: ODF 1.3
Description:
Copied from office-comment list
Original author: Regina Henschel
Original date: Wed, 04 Jul 2012 23:18:35 +0200
Original URL: https://lists.oasis-open.org/archives/office-comment/201207/msg00000.html
the rules for "Criterion" are not clear enough to determine the correct result.
First problem
=============
A1 contains the string "1234".
A2 contains the number 1234.
B1 contains the number 1.
B2 contains the number 2.
HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is true.
=SUMIF(A1:A2;"1234";B1:B2) results in what?
In section '4.11.8 Criterion' I read
<quote>
A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents.
</quote>
So it seems clear "1234" is a text, only A1 matches, because A2 is a number. Indeed a single comparison (A1="1234") results in TRUE, and the single comparison (A2="1234") results in FALSE. So I expect result 1 from B1. Unfortunately, all three Microsoft Excel, Apache OpenOffice and Gnumeric result in 3, which means that they consider A2 to match too.
In the case =SUMIF(A1:A2;1234;B1:B2) which should match cells with number 1234, Gnumeric and Microsoft Excel results in 3, Apache OpenOffice results in 2.
Second problem
==============
Section 4.11.8 contains a description of what is there called 'matching expression'. It is the only place, where I find the term 'matching expression' and its connection to 'Criterion' is not clear.
From the use in existing spreadsheet applications I guess, that it is intended that something like >=1234 should also be possible in SUMIF. But details are missing.
My interpretation: The second parameter of SUMIF can be a single number, a string, a reference to a single cell, or a 'matching expression'. A 'matching expression' is a string that starts with a comparison operator followed be a number, or starts with the sign = or the sign <> followed by a string or followed by nothing (to catch empty cells). All these are valid 'Criterion'. Notice, that the interpretation of "=" would be ambiguous.
What interpretation is intended?
Third example
=============
A1 contains an empty string, for example generated by the formula ="".
A2 is empty
A3 contains the formula ="" which results in an empty string
B1 contains the number 1.
B2 contains the number 2.
=SUMIF(A1:A2;"=";B1:B2) results in what?
With the interpretation, that the second parameter is a 'matching expression' and the description of this case in section 4.11.8, it matches A2 and does not match A1, result is 2. With the interpretation, that the second parameter is the literal character =, it matches neither A2 nor A1, result is 0.
Gnumeric calculates 3, Excel calculates 2, AOO calculates 1.
=SUMIF(A1:A2;"="&A3;B1:B2) results in what?
With the interpretation, that the second parameter is a 'matching expression' it matches A1 and does not match A2, result is 1.
Gnumeric calculates 3, Excel calculates 2, AOO calculates 1.
=SUMIF(A1:A2;"";B1:B2) results in what?
With the interpretation, that the second parameter is a string (the empty string) it matches A1 and does not match A2, result is 1.
Gnumeric and Excel calculate 3, AOO calculates 1.
was:
Copied from office-comment list
Original author: Regina Henschel
Original date: Wed, 04 Jul 2012 23:18:35 +0200
Original URL: https://lists.oasis-open.org/archives/office-comment/201207/msg00000.html
Regina has uncovered an inconsistency in implementations that appears to be due to lack of clarity on our part in defining 4.11.8 Criterion (part 2).
I have copied her analysis into the description to make it easier to reference in commenting further or proposing/discussing resolutions.
Who among the formula crowd wants this one?
> Public Comment: Cannot resolve parameter type "Criterion", which is used in SUMIF e.g.
> --------------------------------------------------------------------------------------
>
> Key: OFFICE-3764
> URL: http://tools.oasis-open.org/issues/browse/OFFICE-3764
> Project: OASIS Open Document Format for Office Applications (OpenDocument) TC
> Issue Type: Bug
> Components: OpenFormula
> Affects Versions: ODF 1.2
> Reporter: Robert Weir
> Fix For: ODF 1.3
>
>
> Copied from office-comment list
> Original author: Regina Henschel
> Original date: Wed, 04 Jul 2012 23:18:35 +0200
> Original URL: https://lists.oasis-open.org/archives/office-comment/201207/msg00000.html
> the rules for "Criterion" are not clear enough to determine the correct result.
> First problem
> =============
> A1 contains the string "1234".
> A2 contains the number 1234.
> B1 contains the number 1.
> B2 contains the number 2.
> HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is true.
> =SUMIF(A1:A2;"1234";B1:B2) results in what?
> In section '4.11.8 Criterion' I read
> <quote>
> A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents.
> </quote>
> So it seems clear "1234" is a text, only A1 matches, because A2 is a number. Indeed a single comparison (A1="1234") results in TRUE, and the single comparison (A2="1234") results in FALSE. So I expect result 1 from B1. Unfortunately, all three Microsoft Excel, Apache OpenOffice and Gnumeric result in 3, which means that they consider A2 to match too.
> In the case =SUMIF(A1:A2;1234;B1:B2) which should match cells with number 1234, Gnumeric and Microsoft Excel results in 3, Apache OpenOffice results in 2.
> Second problem
> ==============
> Section 4.11.8 contains a description of what is there called 'matching expression'. It is the only place, where I find the term 'matching expression' and its connection to 'Criterion' is not clear.
> From the use in existing spreadsheet applications I guess, that it is intended that something like >=1234 should also be possible in SUMIF. But details are missing.
> My interpretation: The second parameter of SUMIF can be a single number, a string, a reference to a single cell, or a 'matching expression'. A 'matching expression' is a string that starts with a comparison operator followed be a number, or starts with the sign = or the sign <> followed by a string or followed by nothing (to catch empty cells). All these are valid 'Criterion'. Notice, that the interpretation of "=" would be ambiguous.
> What interpretation is intended?
> Third example
> =============
> A1 contains an empty string, for example generated by the formula ="".
> A2 is empty
> A3 contains the formula ="" which results in an empty string
> B1 contains the number 1.
> B2 contains the number 2.
> =SUMIF(A1:A2;"=";B1:B2) results in what?
> With the interpretation, that the second parameter is a 'matching expression' and the description of this case in section 4.11.8, it matches A2 and does not match A1, result is 2. With the interpretation, that the second parameter is the literal character =, it matches neither A2 nor A1, result is 0.
> Gnumeric calculates 3, Excel calculates 2, AOO calculates 1.
> =SUMIF(A1:A2;"="&A3;B1:B2) results in what?
> With the interpretation, that the second parameter is a 'matching expression' it matches A1 and does not match A2, result is 1.
> Gnumeric calculates 3, Excel calculates 2, AOO calculates 1.
> =SUMIF(A1:A2;"";B1:B2) results in what?
> With the interpretation, that the second parameter is a string (the empty string) it matches A1 and does not match A2, result is 1.
> Gnumeric and Excel calculate 3, AOO calculates 1.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tools.oasis-open.org/issues/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]