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

    Excel Poisson function in FileMaker

    FrankPottner

      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
          StephenWonfor

          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
            fmpdude

            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
              user19752

              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
                user19752

                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
                  FrankPottner

                  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

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

                  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
                    FrankPottner

                    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
                      user19752

                      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
                        fmpdude

                        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

                         

                        Poisson Distribution | R Tutorial

                         

                        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
                          fmpdude

                          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
                            bigtom

                            I thought poisson distribution meant:

                             

                            1200px-Wash_fish_market.jpg

                            • 11. Re: Excel Poisson function in FileMaker
                              MaxEh

                              Only to developers who work to scale.

                              • 12. Re: Excel Poisson function in FileMaker
                                fmpdude

                                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 

                                 

                                Answer

                                The probability of finishing a checkout in under two minutes by the cashier is 48.7% 

                                 

                                --

                                 

                                Nothing fishy here...