|Summary:||setCellFormula produces incorrect result for FREQUENCY function|
|Product:||POI||Reporter:||Joe Kislo <joekislos>|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
This is the input to my testcase. It has the excel formula already in it
This is the output from my test case. POI has written in it's version of the formula
This is the POI code that writes in the formula to C1
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.