Bug 62373 - #VALUE! result from FREQUENCY formula
Summary: #VALUE! result from FREQUENCY formula
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2018-05-14 14:03 UTC by Will
Modified: 2018-10-18 13:55 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
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.