poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 45752] New: setCellFormula produces incorrect result for FREQUENCY function
Date Fri, 05 Sep 2008 22:47:48 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=45752

           Summary: setCellFormula produces incorrect result for FREQUENCY
                    function
           Product: POI
           Version: 3.0
          Platform: PC
        OS/Version: Linux
            Status: NEW
          Severity: normal
          Priority: P2
         Component: HSSF
        AssignedTo: dev@poi.apache.org
        ReportedBy: joe@k12s.phast.umass.edu


setCellFormula produces incorrect result for FREQUENCY function

If you set this formula using HSSFCell.setCellFormula:
SUM(IF(FREQUENCY($A1:$A5,$A1:$A5)>0,1))

The result in excel will be *incorrect* (it does evaluate without error, but
the result is wrong).  If you look at generated excel file, the result will be
1.  If you click on the formula text, then hit enter, the correct result will
appear.  If you enter the formula in excel manually, the result is correct.

I have attached a testcase which produces this issue.

attached file:poi-formula-issue.xls
contains the input excel sheet
If you run attached file: POISetFormulaIssue.java
it will produce attached file: poi-formula-issue-output.xls

If you look at the output in excel, you can see that C1 and C2 differ.  C1 is
the one written by POI, and C2 was the one I entered in excel in the initial
template.

If you click on C1, and goto Formula Auditing->Evaluate Formula, you can see
how excel evaluates the formula.  It will evaluate the formula differently for
C1 and C2.  It appears the evaluation of FREQUENCY appears to be the issue. 
For C2 it returns an array, and in C1 it just returns 1.

I know this formula looks harebrained, but I need to count the distinct items
in a list.  This is the "microsoft approved" way of doing this:
http://office.microsoft.com/en-us/excel/HP030561181033.aspx

If anybody has any other ways of doing this, I would certainly change my ways
:)

I've looked through bugzilla and there is only one reference to frequency and
it is in bug #21334.  They said their issue was resolved in a previous build,
however I wonder if it simply *stopped crashing*, yet was yielding an invalid
result nonetheless.  

I have reproduced this issue with POI-3.0.2-FINAL and POI-3.1-FINAL.  For some
reason 3.1 is not in the version list in bugzilla, but I have reproduced it
with both.


-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message