[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: [OASIS Issue Tracker] (OFFICE-3764) Public Comment: Cannot resolve parameter type "Criterion", which is used in SUMIF e.g.
[ https://issues.oasis-open.org/browse/OFFICE-3764?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Regina Henschel updated OFFICE-3764:
------------------------------------
Fix Version/s: ODF-Next
(was: ODF 1.3)
> Public Comment: Cannot resolve parameter type "Criterion", which is used in SUMIF e.g.
> --------------------------------------------------------------------------------------
>
> Key: OFFICE-3764
> URL: https://issues.oasis-open.org/browse/OFFICE-3764
> Project: OASIS Open Document Format for Office Applications (OpenDocument) TC
> Issue Type: Bug
> Components: OpenFormula, Part 2 (Formulas)
> Affects Versions: ODF 1.2
> Reporter: Robert Weir
> Priority: Major
> Fix For: ODF-Next
>
>
> 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 was sent by Atlassian JIRA
(v7.7.2#77003)
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]