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