Generating variables with Poisson Distribution

Try:

Sub main()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 100
Cells(i, "A") = random_Poisson(1.25)
Next i
End Sub

Public Function random_Poisson(Mean As Double) As Double
Dim e As Double, p As Double
e = Exp(-Mean)
p = 1
Do Until (p < e)
random_Poisson = random_Poisson + 1
p = p * Rnd()
Loop
random_Poisson = random_Poisson - 1
End Function

@Kaper,

Here's my version of that:

Function RandPois(Lambda As Single, _
                  Optional bVolatile As Boolean = False) As Long
  ' shg 2007

  ' UDF or VBA

  ' Returns a random Poisson variate with variance Lambda
  ' Knuth, Seminumerical Algorithms, p 132

  Dim L             As Single
  Dim k             As Long
  Dim p             As Single

  If bVolatile Then Application.Volatile

  L = Exp(-Lambda)
  p = 1
  Do
    k = k + 1
    p = p * Rnd()
  Loop Until p <= L

  RandPois = k - 1
End Function

It differs from yours in that the loop always executes at least once.

I went back to Knuth, and his description is a little arm-waving. Any thought on which is correct?

Hi shg,
Honestly - I just made a search in my HomeCloud and in one of files there was this function.

But having a brief look (it's almost midnight here):
Initial p is 1
so

Until (p < e)

condition could be met before first execution of loop only for e>1
but as

e = Exp(-Mean)

it means ... for mean <0

Of course, thank you.

My pleasure :-)