Participant

Answered

Spreadsheet Formula Error

9 replies
1070 views
Translate

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:

<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

spreadsheet.

This topic is closed to new replies. Start a new post to keep the conversation going.

H

haxtbhCorrect answer

Inspiring

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

HTH,

^_^

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

Participant

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

Translate

BKBK

Community Expert

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.

Translate

H

haxtbh

Inspiring

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

Translate

WolfShade

Legend

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

HTH,

^_^

Translate

H

haxtbh

Inspiring

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)

Translate

WolfShade

Legend

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,

^_^

Translate

Milind-JoshiAuthor

Participant

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.

Translate

WolfShade

Legend

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,

^_^

Translate

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.