Bug 45752 - setCellFormula produces incorrect result for FREQUENCY function
Summary: setCellFormula produces incorrect result for FREQUENCY function
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-09-05 15:47 UTC by Joe Kislo
Modified: 2015-05-01 20:26 UTC (History)
0 users



Attachments
This is the input to my testcase. It has the excel formula already in it (13.50 KB, application/vnd.ms-excel)
2008-09-05 15:48 UTC, Joe Kislo
Details
This is the output from my test case. POI has written in it's version of the formula (13.00 KB, application/vnd.ms-excel)
2008-09-05 15:49 UTC, Joe Kislo
Details
This is the POI code that writes in the formula to C1 (781 bytes, text/x-java)
2008-09-05 15:50 UTC, Joe Kislo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joe Kislo 2008-09-05 15:47:46 UTC
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.
Comment 1 Joe Kislo 2008-09-05 15:48:59 UTC
Created attachment 22530 [details]
This is the input to my testcase.  It has the excel formula already in it
Comment 2 Joe Kislo 2008-09-05 15:49:20 UTC
Created attachment 22531 [details]
This is the output from my test case.  POI has written in it's version of the formula
Comment 3 Joe Kislo 2008-09-05 15:50:42 UTC
Created attachment 22532 [details]
This is the POI code that writes in the formula to C1
Comment 4 Dominik Stadler 2015-05-01 20:26:32 UTC
I opened the sample documents that you provided and the output-xls displays correctly with "4" in both cells when I open the document with LibreOffice (no Excel on the current machine), whereas I believe you reported that it is "1". So I am not sure how I can reproduce this and thus will close this quite old bug entry as WORKSFORME for now. 

If this is still an issue for you with the latest version of POI then please reopen it with a short explanation of with which version of Excel you see the problem.