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.

Please Login or Register  to view this content.

The formula in Q9 and copied right and down is

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.