|Summary:||#VALUE! result from FREQUENCY formula|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
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.