12 Replies Latest reply on Jul 5, 2017 3:32 AM by fmpdude

# Excel Poisson function in FileMaker

I'm looking for some ideas on how to create the equivalent of the cumulative distribution Excel Poisson function in FileMaker.

The function takes 2 parameters; X and Lambda and is described here;

https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_POISSON_function

It evaluates to the sum of the following formula from 0 to X;

Exp(-1 * Lambda)  *   (Lamda ^ X)    /     Factorial(X)

The problem is when Lambda is greater than 37 the formula returns 0 due to the first part ie

Exp(-37) = 1.0e-16

Exp(-38) = 0

and Factorial(X) returns "?" with values higher than 212.

I was hoping to find a Javascript equivalent of this function.

My plan was to;

- in a global field create a HTML/Javascript page that evaluates the function

- use FileMaker's WebViewer to read the page & result

I haven't had any luck finding a Javascript equivalent to the Excel function.

I did find some sample code with the limits on Lambda at around 100 which is better but not great.

The result of the Poisson function needs to be update immediately as X or Lambda is changed, and the solution needs to be WebDirect compliant.

Does anyone have any suggestions?

• ###### 1. Re: Excel Poisson function in FileMaker

Frank

This function (FileMaker Custom Function:RandomPoisson ( mean ) ) at Brian Dunnings site has the word "Poisson" in it.  You might be able to deconstruct it.

Stephen

• ###### 2. Re: Excel Poisson function in FileMaker

What I did was to link FileMaker to "R" statistical software via a micro-service (INSERT FROM URL).

The Poisson was one of the simple statistical calculations I added.

Since I don't know your programming background, I don't know if this information is useful, but what you want to do is definitely doable (for FREE).

There are a few dependencies:

1. You'd have to install RServer and have it running.

2. You'd have to create a small REST service in (I used) Java.

3. You'd have to write the Java code to call R (I could give you what I used).

4. You'd have to wire in the INSERT FROM URL to call the REST service.

.

.

.

Then run goodness-of-fit tests to make sure you actually have a Poisson distribution.

---

Say you're a golfer, and you want to calculate the probability that you'll hit the fairway exactly 7 times with Mu = 5.

Using the service approach, you can calculate that in FileMaker, Terminal, or any application that will let you do a GET.

### In Terminal...

Ans (assuming Poisson underlying dist, of course):

Probability Distributions in R (Stat 5101, Geyer)

If my example isn't the exact version of the Poisson you need, check out the link directly above for the various R Poisson functions.

BTW, 212! is a HUGE number.

Even 100! is ...

Using special programming techniques, there are ways to represent an arbitrarily large number (with NO limit), so even 500! would be no problem, though you wouldn't call the standard factorial function.

## Here is 1200!

63507890863456767124026223135865363993920361927048401849732641593094

11830433466483851848927976120285811044265619896142388833778595837405439587509

00081793946021217290487555275062223886204383980125566150619409099041150151644

83186340282667999681601237310950712039020066375755766076440811303940538840103

89074195191756419056127539687320412293708374609864041458885011610722567552041

25844153591551034956552085340546581491462037397580273260033548664567310975610

866208899037172468188074901316781582994019012350575143288816772041500548880018

438701886167566944683796770332286393499742198006917602366748087724591478497613

197722333492852581853548814627067590164168445080265934683785449522543023040683

256339058006178460921733163349204239769732194533329026164772563063272333553052

874683807459092084159805135771592438765826488751448384540346188028396341849943

084009678562363661075632731857027792958610811005805698902918394030806921525625

497504645189412966155890729619014458671016102669128532889083041843512480844499

867278377141647248869347134038409283940715816485590371755522790547652681913365

629999188278538429846948559229991629076795619310977877713246378934568230825241

7901824650495540580312177112136075226441478103971845509811845735181552544196255

3663917394824907930942411732997508709279776658345871568600666174624864566227235

9921875986601309536409363453508733397446824150895635011195164429996765327793071

0849217692543973186034737251111243360479099275932519768133972634932205487825511

98746259658501969406606486859906712102125722255642283461590537547136679347561778

80507837712524591347066833930606771513277248914447108453842428044821209156242191

48131623994845631407143711128227075649481344333670737252501702467855919959051586

03609621364671835813517990819259045815304700135156292217699848259584162249449828

67113744499809912183809641523923237883957287541972186804232110652653006828947123

64723139310071615478543804492659648948106140444494995885034202521403870058241167

58472961952853683131349288562207261244623513340984150590732752399537357184250861

23827271757497468773885713767925771130546617686545287963633612898691413349119011

210341661317374809835898724194978360650558823413986166888783608735534361048197825

992486540004672367499044204531085976637277941222039312514997381320707709813267213

8444005282659172419055200305671875271213059032326984695023198282884976420717895445

5947067287689668281698617685255361391212397613520854063319157713421292376471988326

0091745561140753211368159375154389149276613870478944559132118639123292629448861220

1666926687958699988155366978431687024351547628191190557007819168998473319522603686

0806268667819790226419172230117837373854372022358111636637807064986898672422564081

9288848872888236573440404740650405057575066492961346462735749115329710082527101393

6541637312093111393194457203035774499775525358887063152647560104895435143828289260

0327423792509316606246095159296000000000000000000000000000000000000000000000000000

00000000000000000000000000000000000000000000000000000000000000000000000000000000000

00000000000000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000000000000000000000000000000000000000000

---

If you send me a full data example, I'll let you know what R comes up with at least with regular values.

2 of 2 people found this helpful
• ###### 3. Re: Excel Poisson function in FileMaker

There is limit of number about e400 in FM, so Factorial() can't progress, but Exp() can

1 of 1 people found this helpful
• ###### 4. Re: Excel Poisson function in FileMaker

There is no need to use Factorial() in custom function.

Case (

X < 1 ; "ERROR" ;

X = 1 ; SetPrecision ( Exp ( - Lambda ) * Lambda ; 400 ) ;

Lambda / X * Poisson ( Lambda ; X - 1 )

)

Excel function use parameter ordered x, lambda...

• ###### 5. Re: Excel Poisson function in FileMaker

First off let me say thank you for pointing out "SetPrecision"; I've been using FileMaker for close to 20 years but hadn't noticed that function before.

Learn something new every day!

As far as I can tell your function returns the "probability density", not the "cumulative distribution" which is what I'm looking for.  In Excel / Open Office

Poisson(1, 1, 0) = 0.3679  (probability density)

Poisson(1, 1, 1) = 0.7358  (cumulative distribution)

However, I can't figure out how you changed the formula for the probability density which is shown in the URL

as

Exp(-1 * Lambda)  *  Lambda ^ X   /   Factorial(X)

to

Case (

X = 1 ; Exp ( - Lambda ) * Lambda;

Lambda / X * Poisson ( Lambda ; X - 1 )

)

Is there also another way of calculating the cumulative distribution?

• ###### 6. Re: Excel Poisson function in FileMaker

This sounds like exactly what I need IF the "SetPrecision" function still doesn't give me the flexibility that I need.

I'm assuming this tool also has the function that calculates X given Lambda and Assurance Level?

• ###### 7. Re: Excel Poisson function in FileMaker

So I misread that X can be 0, change the Poisson() function as

Case (

X < 0 ; "ERROR" ;

X = 0 ; SetPrecision ( Exp ( - Lambda ) ; 400 ) ;

X = 1 ; SetPrecision ( Exp ( - Lambda ) * Lambda ; 400 ) ;

Lambda / X * Poisson ( Lambda ; X - 1 )

)

then PoissonC() function is which you need

Case (

X < 0 ; "ERROR" ;

X = 0 ; Poisson ( Lambda ; 0 ) ;

Poisson ( Lambda ; X ) + PoissonC ( Lambda ; X - 1 )

)

//attention, calling both Poisson() and PoissonC()

Not much tested.

Exp(-1 * Lambda)  *  Lambda ^ X   /   Factorial(X)

is

Exp(-1 * Lambda)  *  (Lambda * Lambda * ... X times )  /  (1 * 2 * ... X )

then

Exp(-1 * Lambda)  *  (Lambda / 1 ) * ( Lambda / 2 ) * ... ( Lambda / X )

so become recursive function called X (-1) times.

2 of 2 people found this helpful
• ###### 8. Re: Excel Poisson function in FileMaker

in R, which is easy to connect to FMP...

The probability of having sixteen or less cars crossing the bridge in a particular minute is given by the function ppois...

ppois(16,lambda=12, lower=FALSE) = 0.101291

----------

The inverse is just 1 minus this number (cumulative):

Hence the probability of having seventeen or more cars crossing the bridge in a minute is in the upper tail of the probability density function ...

ppois(16,lambda=12) = 0.898709

IMHO, if you're doing statistics, don't try to re-create functions in a CF. Use a real stat program with FMP.

FMP's set precsion doesn't do what I thought it does.

For example,

SetPrecision ( 1/3 ; 1000 ) = > 400 max positions.

Towit,

.3333333333333333333333333333333333333333333333333333333333333333333333333

33333333333333333333333333333333333333333333333333333333333333333333333333

33333333333333333333333333333333333333333333333333333333333333333333333333

333333333333333333333333333333333333333333333333333333333333333333333333333

333333333333333333333333333333333333333333333333333333333333333333333333333

33333333333333333333333333333

• ###### 9. Re: Excel Poisson function in FileMaker

Just wanted to follow up that R handles the "large x" problem fine.

For example, the probability of exactly 600 occurrences with a mean (mu) = 100 is: 2.939442e-252

For results much smaller than that, I would use custom programming techniques and write my own stat Poisson function using the formula -- but I can't see any real-world case where I would need to do that.)

Here's a final posting showing a FileMaker Solution with different Poisson statistics, including what you would get in Excel (but with larger occurrences supported!)

CLICK ON IMAGE IF TRUNCATED ON RIGHT SIDE

The cumulative probability P[x<=2], for example is just:

P[x=0] + P[x=1] +P[x=2]

---

HOPE THIS HELPS.

• ###### 10. Re: Excel Poisson function in FileMaker

I thought poisson distribution meant:

• ###### 11. Re: Excel Poisson function in FileMaker

Only to developers who work to scale.

• ###### 12. Re: Excel Poisson function in FileMaker

Well, given a Poisson process, the inter-waiting times between successive queue entries follows an Exponential Distribution, so, OK.

Exponential Distribution -- from Wolfram MathWorld

Seriously, though, the exponential distribution is majorly important.

Here's a good example: Exponential Distribution | R Tutorial

#### Problem

Suppose the mean checkout time of a supermarket cashier is three minutes. Find the probability of a customer checkout being completed by the cashier in less than two minutes.

#### Solution

The checkout processing rate is equals to one divided by the mean checkout completion time. Hence the processing rate is 1/3 checkouts per minute. We then apply the function pexp of the exponential distribution with rate=1/3.

> pexp(2, rate=1/3)
[1] 0.48658