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: [email protected]
ReportedBy: [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
