I am using ColdFusion 11 and trying to use FDIST function in a spreadsheet to get F-distribution value. The error I'm getting is that, it does not like the semicolon in the formula. In the exception.log file I see this error, org.apache.poi.ss.formula.FormulaParseException: Parse error near char 7 ';' in specified formula 'FDIST(1;4;5)'. Expected ',' or ')'
The simplified code is as follows:
<cfset sObj = SpreadsheetNew()>
<cfset sFormula = "FDIST(1;4;5)">
<cfset spreadsheetSetCellFormula(sObj, sFormula, 1, 1)>
<cfset iValue = spreadsheetGetCellValue(sObj, 1, 1)>
<p>Value = #iValue#</p>
Please let me know how this can be fixed. Any ideas?
Info about FDIST function Documentation/How Tos/Calc: FDIST function - Apache OpenOffice Wiki
1. The error message indicates that it was expecting a comma. Replace your semi-colons with commas.
2. I believe that CF natively supports Excel, not OpenOffice Calc, and Excel uses commas instead of semi-colons in FDIST().
WolfShade and haxtbh: Thanks for the reply. I already had tried using comma instead of semicolon but it does not work. It does not evaluate the function.
As far as I know, CF supports OpenOffice integration. That is the reason I used Calc (which uses semicolon and not comma).
Hope it makes sense.
CF natively supports Microsoft Excel. When you use SpreadsheetNew(), you are creating a Microsoft Excel object.
In CFAdmin, there is a section under Server Settings > Document that will allow you to map to an OpenOffice directory on the server, or on a remote host; but if it isn't populated, OpenOffice isn't supported.
If changing the semi-colons to commas didn't fix it, I'm not sure what to do. What is the error message that appears when commas are used? Use CFTRY/CFCATCH/CFDUMP to display the error (I prefer using this method as opposed to the standard CF error.)
Have you tried using it with , instead? The function uses commas in Microsoft based spreadsheets and I think the spreadsheet functions in Coldfusion are based off of the Microsoft Excel standard - F.DIST(1,4,5)
This could be the case. I can confirm that it doesn't work in CF 2016 either. Returns the same 0.0 value
I think I had read, somewhere, that in Excel FDIST() was being replaced by F.DIST() and F.DIST.RN().
Coldfusion doesn't recognise those formulas either.
I been speaking to a guy at adobe, he is pretty sure this is a bug.
Although it seems that CF uses the Apache POI to do all the work behind the scenes. The Apache POI does NOT currently support FDIST as formula. - Developing Formula Evaluation