Bug 45752

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.