Spreadsheet Formula Error

[Milind-Joshi]

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)>

<cfoutput>

<p>Value = #iValue#</p>

</cfoutput>

Please let me know how this can be fixed. Any ideas?

Info about FDIST function Documentation/How Tos/Calc: FDIST function - Apache OpenOffice Wiki

Thanks.

Milind

[WolfShade]

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().

HTH,

^_^

[Milind-Joshi]

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.

[WolfShade]

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.)

HTH,

^_^

[haxtbh]

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)

[BKBK]

I wonder whether FDIST is too new for the version of Excel in Coldfusion 11. In any case, you get a wrong result (0.0) rather than an error. Please report it as a bug.

[haxtbh]

This could be the case. I can confirm that it doesn't work in CF 2016 either. Returns the same 0.0 value

[WolfShade]

I think I had read, somewhere, that in Excel FDIST() was being replaced by F.DIST() and F.DIST.RN().

HTH,

^_^

[haxtbh · Accepted solution]

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

[Milind-Joshi]

Thanks guys. I have submitted it as suggested by BKBK. The link to the bug report is Bug#4183980 - Spreadsheet Formula Bug