Bug 49538

Summary: [PATCH] Implementing a excel predefined function POISSON
Product: POI Reporter: Kalpesh Parmar <kparmar1>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal CC: kparmar1
Priority: P2    
Version: 3.7-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: This is the zip of the patch file and the junit test.
[Patch] The corrected Poisson implementation function
[Patch] The re-corrected Poisson implementation function

Description Kalpesh Parmar 2010-07-01 13:12:07 UTC
Created attachment 25678 [details]
This is the zip of the patch file and the junit test.

I have implemented the Poisson function ( within the NumericFunctions ).  This also had the implication that I needed to use another library ( common-math - as I need not want to reinvent ) but I have updated the ant build.xml appropriately. 

I have also supplied a unit test for this function.  I have only done this very quickly therefore ( although it does work - with validation and a unit test) it would be good for someone to verify that my changes are ok within your framework.

I have attached the addition file (unit test) but it seems that when I created the patch it has included this new file in it (I have used tortoiseSVN to create the patch).

The zip poi-hssf-poisson-patch.zip is attached.

Hope this is helpful.  If not please ask for any other info.


Comment 1 Kalpesh Parmar 2010-07-06 06:00:46 UTC
I have updated the patch as I missed out the case where if x is a decimal excel will automatically truncate it (now it is compliant with the excel definition of the Poisson function)


I have (as before) attached the zip file which contains the patch ( created as before ) and the updated junit test.

poi-hssf-poisson-patch2.zip is now the one to use.


Comment 2 Kalpesh Parmar 2010-07-06 06:03:07 UTC
Created attachment 25712 [details]
[Patch] The corrected Poisson implementation function

Update of the first patch I posted.  I have added additional details below.
Comment 3 Kalpesh Parmar 2010-07-07 04:43:47 UTC
Comment on attachment 25712 [details]
[Patch] The corrected Poisson implementation function

There is an updated patch that should be used.  See details for the next attachment.
Comment 4 Kalpesh Parmar 2010-07-07 04:51:23 UTC
Created attachment 25725 [details]
[Patch] The re-corrected Poisson implementation function

Current version for the Poisson implemented fuinction.
Comment 5 Kalpesh Parmar 2010-07-07 04:52:20 UTC

I have provided yet another update for the patch I posted.  The reason is that excel implementation for Poisson for x - 0 and mean - 0 is 1.  This is different to that of commons implementation ( it errors which actually is what should happen ).  
I have followed the result that excel gives and now return a 1 in this case ( this I do not believe is right but its what excel does so I am being consistent with excel).

The zip poi-hssf-poisson-patch3.zip is attached above ( contains the patch and updated junit test).


Comment 6 Yegor Kozlov 2010-07-26 08:24:56 UTC
Thanks for the patch, applied in r979255

We had a debate whether to depend on commons-math or not and we think in the case of POISSON  we can do without third party libraries, the implementation of Poisson distribution is really a few lines of code: 

        private double probability(int k, double lambda) {
            return Math.pow(lambda, k) * Math.exp(-lambda) / factorial(k);

        private double cumulativeProbability(int x, double lambda) {
            double result = 0;
            for(int k = 0; k <= x; k++){
                result += probability(k, lambda);
            return result;