Bug 62373

Summary: #VALUE! result from FREQUENCY formula
Product: POI Reporter: Will <skarjones>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Will 2018-05-14 14:03:12 UTC
The following formula is returning #VALUE! when I open the spreadsheet in Excel: 
setCellFormula("SUMPRODUCT(--(FREQUENCY(MATCH(" + rangeString + "," + rangeString + ",0),ROW(" + rangeString + ")-ROW(" + colString + finalFirstDataRowIndex + 	")+1)>0))");

If I go into the cell and back out, it calculates properly. A colleague tried OpenOffice, and it worked fine. I added setForceFormulaRecalculation(true), but it didn't help. Clicking the Recalculate All button in Excel also didn't replace the #VALUE! with the proper calculation. Only going into and back out of the cell worked. I tried calculating the formula in Java before creating the workbook, but FREQUENCY is not supported.
Comment 1 Nick Burch 2018-05-14 14:40:44 UTC
Frequency looks to be a function that can run on either as a "normal" function on a range (returning a single value), or as as "array" function (returning multiple values)

I *think* that adding support for the non-array version ought to be fairly easy to do, if you want to have a try. Have a read of http://poi.apache.org/spreadsheet/formula.html then http://poi.apache.org/spreadsheet/eval.html then http://poi.apache.org/spreadsheet/eval-devguide.html , next http://home.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx , implement the function, then finally http://poi.apache.org/guidelines.html for contributing it back!
Comment 2 Yegor Kozlov 2018-10-18 13:55:07 UTC
I checked in support for FREQUENCY in r1844238. Please test with the latest build from trunk.

If evaluation still doesn't work then attach a sample spreadsheet and Java code to reproduce the problem. Not implemented FREQUENCY might not be the only problem.