Poisson distribution

New to macro in excel. Need to implement Poisson distribution in a macro. The attached sheet has details. Need the stock level for 80% 90% 95%. So basically a number in k5 that will generate 80% Poisson Probability. This can be done manually ofcourse. Need to automate this process for the values in columns L M N.Obviously ignore 0 cells in those columns.


For the first case stock level 2 is close to 84% instead of 80% which will work. But there wil be no stock level for 90% and a stock level of 3 fore 95%.

In short is there a way to fill out Q R S through a macro?

I don't understand your data ...

Have you looked at the POISSON function?

I retract that question -- I see you have a POISSON formula in there.

I think you want a Poisson inverse function (and I have one), but still don't understand why you have three columns of numbers in L-M-N.

Originally Posted by shg

I retract that question -- I see you have a POISSON formula in there.

I think you want a Poisson inverse function (and I have one), but still don't understand why you have three columns of numbers in L-M-N.

LMN contain rate/values in year 1 year 2 and year 3 for certain item.so the first E(x) value would be L9 by Q5.E(x) is cell m5 so the formula is technically POISSON(K5,M5,TRUE). The stocking number will be greater than the result of e(x). I will keep changing until i get 80%-85% in cell k4. so basically I'll fill out Q9 with close enough stocking number and then repeat for 90% and 95%. then move onto next row until i fill out all the columns through Q9 to S296.

       ---L---- ------M------- N O -P-- -Q- -R- -S-
   4            Expected value                     
   5               5.408609321     Size 100        
   6                                               
   7                                               
   8          1              2 3        80% 90% 95%
   9   0.013522              0 0          1   2   2
  10   0.013522              0 0          1   2   2
  11   0.013522              0 0          1   2   2
  12   0.027043              0 0          3   4   5
  13   0.054086              0 0          6   7   8

The formula in Q9 and copied right and down is

=PoissonInv(Q$8,$Q$5*$L9)

Function PoissonInv(dVal As Double, dMean As Double) As Variant
    ' shg 2011

    ' For a Poisson process with mean dMean, returns the largest
    ' integer N such that POISSON(N, dMean, True) <= dVal

    ' E.g., POISSON(5, 10, TRUE) returns 0.0670859629
    ' PoissonInv(0.0670859629, 10) returns 5

    ' Returns #VALUE! if dVal < 0 or dVal >= 1
    ' Returns -1 if dVal < POISSON(0, dMean, TRUE)

    Dim iX          As Long     ' number of events
    Dim dCDF        As Double   ' cumulative distribution function of iX

    ' these variables are used to simplify this summation:
    '    dCDF = dCDF + Exp(-dMean) * dMean ^ iX / .Fact(iX)
    Dim dExpMean    As Double   ' =Exp(-dMean)
    Dim dFact       As Double   ' incremental factorial
    Dim dPowr       As Double   ' incremental power

    If dVal < 0 Or dVal >= 1 Then
        PoissonInv = CVErr(xlErrValue)

    ElseIf dVal > 0 Then
        dExpMean = Exp(-dMean)
        dFact = 1
        dPowr = 1

        Do While dCDF < dVal
            dCDF = dCDF + dExpMean * dPowr / dFact
            iX = iX + 1
            dFact = dFact * iX
            dPowr = dPowr * dMean
        Loop

        PoissonInv = iX - IIf(dCDF / dVal > 1.000000000001, 2, 1)
    End If
End Function

This looks great. Can you Please upload this file with code.


---L---- ------M------- N O -P-- -Q- -R- -S-
4 Expected value
5 5.408609321 Size 100
6
7
8 1 2 3 80% 90% 95%
9 0.013522 0 0 1 2 2
10 0.013522 0 0 1 2 2
11 0.013522 0 0 1 2 2
12 0.027043 0 0 3 4 5
13 0.054086 0 0 6 7 8

According to my excel poisson formula at row 13 stock level 7 gives 82% and 9 gives 95% . Does the inv Poisson calculate a bit differently.

What i was hoping for was to include a condition to chose stock level number from 76%-85% as 80% , 86%-92% as 90 and 93%-96% as 95. Can we put some code in this macro that makes this possible.

According to my excel poisson formula at row 13 stock level 7 gives 82% and 9 gives 95%.

PoissonInv returns the largest number of events whose cumulative probability is <= the value you specify, which is exactly consistent with the results.

If your goal is to stock at 80%/90%/95% confidence, add one to those numbers.

I don't understand your confusion. Point to one result you don't understand and explain why.

Here's a clean-up of that function:

Function PoissonInv(dProb As Double, dMean As Double) As Variant
    ' shg 2011, 2012-0519

    ' For a Poisson process with mean dMean, returns the largest
    ' integer N such that POISSON(N, dMean, True) <= dProb

    ' E.g., POISSON(5, 10, TRUE) returns 0.0670859629
    ' PoissonInv(0.0670859629, 10) returns 5

    ' Returns #NUM! if
    '   dProb < 0 or dProb >= 1
    '   Exp(-dMean) = 0 (i.e., dMean > 746)
    '   The factor dK blows up
    
    ' Returns -1 if dProb > POISSON(0, dMean, TRUE) = Exp(-Mean)

    Dim N           As Long     ' number of events
    Dim dCDF        As Double   ' cumulative distribution function at N

    ' These two variables are used to simplify this summation:
    '    dCDF = dCDF + Exp(-dMean) * dMean ^ N / N!
    Dim dExpMean    As Double   ' =Exp(-dMean)
    Dim dK          As Double   ' incremental power & factorial

    If dProb <= 0# Or dProb >= 1# Then
        PoissonInv = CVErr(xlErrNum)

    Else
        dExpMean = Exp(-dMean)
        
        If dExpMean = 0# Then
            ' dMean is too large ...
            PoissonInv = CVErr(xlErrNum)

        ElseIf dExpMean > dProb Then
            ' dProb > POISSON(0, dMean, TRUE)
            PoissonInv = -1&

        Else
            dK = 1#
            On Error GoTo Oops
            
            Do While dCDF <= dProb
                dCDF = dCDF + dExpMean * dK
                N = N + 1&
                dK = dK * dMean / N
            Loop

            PoissonInv = N - 2&
        End If
        Exit Function
        
Oops:
        ' dK blew up ...
        PoissonInv = CVErr(xlErrNum)
    End If
End Function