Generate Random numbers from Poisson distribution

**samot79** · 02-24-2017, 04:55 AM

Hi,

I am writing a sub that where I have to generate numbers from the Poisson distribution with different values of Lambda.

I can do this in a worksheet by add Data Analysis add in. I use the random number generation and specify my lambda, number of variables and number of generated numbers.

Does anyone know how I can access this function from VBA?

Or better up, anyone with a written function for this?

**sweep** · 02-24-2017, 05:32 AM

Hi,

I don't claim credit for this (can't remember from whence it came!) but I think it will work for you.

Please Login or Register  to view this content.

**samot79** · 02-24-2017, 05:44 AM

Hi,

I have seen this function elsewhere, but something is wrong with it.

I cant say I understand why but function will always return 0 if lambda <= 0.5

If I had statement debug.print in loop b wont increase by one on each iteration.

**MrShorty** · 02-24-2017, 11:50 AM

If I understand the problem with the posted UDF, the mistake is so obvious that I am not going to give it away. Instead, I am going to suggest that this is a good opportunity to practice using VBA's debugging tools and help you find the issue yourself (if you are unfamiliar with VBA's debugging tools, I recommend something like this: http://www.cpearson.com/excel/DebuggingVBA.aspx ). Debugging steps I took:

1) I will almost always add a Stop statement of some kind to the UDF so that I can enter debug mode. In this case, I added

Please Login or Register  to view this content.

right after the function statement.
2) Then I entered the function in B1 of the spreadsheet =PoissonRand(A1) and entered 0.5 into A1.
3) When the function entered debug mode, the first thing I did was look at the locals window to see if lambda had been correctly passed to the function. At this point, I saw something other than 0.5 for lambda, and the error was obvious to me. Follow these steps and see what value is stored in lambda when you send 0.5 to the function.

A couple of other thoughts.

If you want this to be volatile -- so that it generates new random numbers each time Excel calculates -- then you can add

Please Login or Register  to view this content.

to the function. As currently programmed, the function will only calculate when lambda changes (or some other full calculate event is triggered). You can decide what behavior you want from this. Personally, I would tend to want this to be like Excel's built in random number functions and would make it volatile.

Wikipedia describes this and two other algorithms for generating random Poisson numbers from uniform random numbers https://en.wikipedia.org/wiki/Poisso...ndom_variables They indicate that this algorithm works well, but may have some troubles for large lambda, and they suggest alternatives pseudocode. Only you know if this will be a concern for your implementation. If lambda can be large, then it would seem prudent to investigate these other algorithms in order to program a robust Poisson random number generator.