Highlighted

Spreadsheet Formula Error

New Here ,
Aug 19, 2016

Copy link to clipboard

Copied

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

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

Views

429

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Spreadsheet Formula Error

New Here ,
Aug 19, 2016

Copy link to clipboard

Copied

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

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

Views

430

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 19, 2016 0
LEGEND ,
Aug 23, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2016 0
New Here ,
Aug 23, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2016 0
LEGEND ,
Aug 23, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2016 0
Advocate ,
Aug 23, 2016

Copy link to clipboard

Copied

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)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2016 0
Adobe Community Professional ,
Aug 24, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 24, 2016 0
Advocate ,
Aug 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 25, 2016 0
LEGEND ,
Aug 25, 2016

Copy link to clipboard

Copied

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

HTH,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 25, 2016 0
Advocate ,
Aug 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 25, 2016 0
New Here ,
Aug 25, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 25, 2016 0