+ Reply to Thread
Results 1 to 37 of 37

Creating a zero inflated poisson in excel

  1. #1
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Creating a zero inflated poisson in excel

    In trying to develop a model in excel to predict football outcomes (1X2,Over/Under,Both Teams to Score/Both Teams not to score), I realized that the probability of draws and the probability of zero is underestimated when using Poisson Distribution. But after doing some search online, I kept coming across suggestions that using the zero-inflated Poisson can improve the accuracy of the results.

    Question:
    Is it possible to create a zero-inflated Poisson in Excel? I will appreciate all the help I can get.

    Attached is my workbook:

    Poisson.xlsxPoisson.xlsx

    Note: Data in workbook obtained from below website:

    https://www.sbo.net/strategy/footbal...-distribution/
    Last edited by gko_87; 05-22-2017 at 05:39 AM.
    OnditiGK

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    The statistics of this are above my pay grade, but here's what I found. The zero inflated poisson model seems to boil down to a hybrid between the binomial distribution to explain the zero values and the Poisson distribution to explain the non-zero values. It seems to me that, if you have your poisson and binomial formulas, then you simply need to combine them in an IF() function that will choose (based on whether x=0 or not) which formula to apply to a given point.

    This looks like a good resource describing the process (and the only one I found in a cursory internet search that described the process in Excel): https://www.casact.org/pubs/forum/09...nn_francis.pdf

    There are several other discussions out there, but most of them, if they looked into implementation, considered implementation in R or stata or other non-Excel application. This model might be preprogrammed in these other, more robust, statistics packages, so they may be preferable to Excel for this analysis.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Thank you. I have really been waiting for someone to reply to this and the wait has been too long. I will have a look at the resource then get back here.

    Thanks again.

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Creating a zero inflated poisson in excel

    ...and the wait has been too long
    The wait was excellent value for money - if you want faster then consider posting (and paying) in the Commercial Services sub-forum.

  5. #5
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Still waiting and hoping that someone will help me out. Much of the material about the subject is too complicated to absorb.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    Yes, it can be difficult to quickly absorb this kind of information. I am not sure if any of our regular users here has previously absorbed this kind of model. I'm sure that, if I spent enough time with it, I could absorb it, but I would also expect that you could absorb it if you spent enough time with it.

    To help us help you, is there a specific part or step that you are having trouble with? It seems to me that the key starting point (after understanding the preliminary discussion leading up to it) is Eqn. 2.1 of the Flynn and Francis article (https://www.casact.org/pubs/forum/09...nn_francis.pdf ). What part of this equation are you having trouble translating into Excel?

  7. #7
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    All I see in the article are equations and there are no practical examples where the symbols are replaced with sample values and there are no straight forward practical examples. MrShorty,I understand that you might be busy but I will really appreciate if you could guide me through these steps and and if possible how these suggestions can be implemented in excel. I am mostly interested on how the principles in the article can be applied in simulating football data (English Premier League).

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    All I see in the article are equations
    Did you understand the equations? I find that I must understand the equations before I can program them into the spreadsheet.

    there are no straight forward practical examples.
    In the paragraph after Equation 2.1, they point the reader to appendix G that has their worked example. Appendix G is nearly at the end of the document (before the "references" section). Did you scroll down and study appendix G? Can you see how they put together (and recreate in your own copy of Excel) table G.1? If it helps understand G.1, it appears that column 4 is equation 2.1. There also appears to be an error in the description of column 5. I think it should be column2/column3/(1-column3). Where do you get stuck?

  9. #9
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    I am headed to print out the document so I can go through it. Will appreciate if you can create time so we can go through it together.

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    I'm not too far from going to bed for the night, but I will be around off and on again starting in about 8 or 9 hours. Spend some time with equation 2.1 and appendix G. Once you can recreate their analysis/regression, then you can start to think through how this will apply to your football data.

  11. #11
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    I am having difficulty calculating values for column 4. Attached is my workbook with data for column 4 entered manually.

    ZIP MODEL.xlsx
    Last edited by gko_87; 05-23-2017 at 06:16 AM.

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    What formulas did you try for column 4. Eqn 2.1 is a two part equation. Since there is only the one entry for x=0, I would enter 2.1.a into E9. Then enter 2.1.b into E10 and copy E10 down. I would have expected E9 to look something like =$C$4+(1-$C$4)*exp(-$C$5). E10 might have been (1-$C$4)*$C$5^B9/FACT(B9)*exp(-$C$5) (note the mix of relative and absolute references to make copying easy). It also looks like you are having trouble with column 5, due to the authors' error. I think it should be C9/D9/(1-D9)

  13. #13
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    By 1 is the author not referring to column 1? This is how I understand it. I don't see you substituting 1 for column 1.

    You also confused me a bit by:

    "I would enter 2.1.a into E9. Then enter 2.1.b into E10 and copy E10 down. I would have expected E9 to look something like =$C$4+(1-$C$4)*exp(-$C$5). E10 might have been (1-$C$4)*$C$5^B9/FACT(B9)*exp(-$C$5)"

    By the above do you mean that I should enter 2.1 a into E9 and 2.1 b into E10? I realize that both formulas give the same answer.
    Last edited by gko_87; 05-23-2017 at 09:11 AM.

  14. #14
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Here is my final table for Table G.1 (Page 218 of the document). Corrections made based on your suggestions in post #12.

    See attached:

    Attachment 519564

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    By the above do you mean that I should enter 2.1 a into E9 and 2.1 b into E10? I realize that both formulas give the same answer.
    Yes, that is correct. At this point in the analysis, both equations should give the same answer (it should be a simple algebra exercise to show that when phi=0 and x=0, both equations must give the same answer). Later in the analysis, when phi<>0, then they will give different results for x=0.

    By 1 is the author not referring to column 1? This is how I understand it. I don't see you substituting 1 for column 1.
    It is kind of confusing the way the author's did this. If you look closely, then enclose column numbers in parentheses, but regular numbers are not. So (1) refers to column 1 (should be (2)) and 1 refers to the number 1.

    Your latest attachment failed to attach. If your spreadsheet matches the values in Table G.1, then you probably have the formulas correct, and you are ready to move on to setting up the Solver model to find phi and lambda.

  16. #16
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Here it is:

    ZIP MODEL.xlsx

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    Two notes:

    1) You will need to copy the sum formula in G15 over to H15, since the sum of the weighted square deviations is what will be used as our objective function to minimize in the Solver model.

    2) I note that you used equation 2.1 b in all of column 4, and did not include 2.1 a anywhere. From what I have understood, 2.1 a is the key equation that makes this a "zero inflated" Poisson model. Using only 2.1 b, you will get something like a regular Poisson distribution, but not a "zero inflated" Poisson distribution. It is ultimately up to you which model you want to use, but your OP specifically requested the zero inflated Poisson, so I would expect you to want to use 2.1 a in E9.

    Other than those things, It looks ready to move on to the Solver regression step.

  18. #18
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Are these the adjustments you suggest I make? Because using 2.1 a in E9 gives 0.44043 and 2.1 b produces the same answer in E9 which is 0.44043 (E9=E10). I am a bit confused here please tell me what I am missing. Which of the two (E9and E10) do I auto-fill?

    Copy of ZIP MODEL2.xlsx

  19. #19
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    Yes, though I notice in this version, your formula in E10 is referring to B9 instead of B10 (and so on down the column). It should be referring to the same row (E10 refers to B10, E11 refers to B11, and so on).

    I am a bit confused here please tell me what I am missing.
    I'm not sure what you are missing. On the surface, it appears that you are missing the part of equation 2.1 that describes when to use 2.1 a and when to use 2.1 b. Note that equation 2.1 specifies that one should use 2.1 a when x=0 and 2.1 b when x>0. Even though the two equations give the same result when both phi=0 and x=0, this will not be true when phi is not longer 0 (try putting 0.1 in for phi and see that 2.1 a is different from 2.1 b in E9).

    Beyond that, it seems possible that you have not understood the discussion leading up to equation 2.1 that explains the reasons for using a zero-inflated model. I cannot explain it any better than these authors, so I'm not sure what to recommend if you are having difficulty understanding the basic idea behind zero inflated models.

  20. #20
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Thank you for pointing this out. How about this. I really want to make sure that I have everything correct before moving to the next step:

    Copy of ZIP MODEL2.xlsx

  21. #21
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Is x referring to values in B9 to B14?

  22. #22
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    Post 20 looks like an exact match to Table G.1, so I think it is ready to go on.

    Yes, x is referring to B9:B14.

  23. #23
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Thank you so much for the patience. What do I do next?

  24. #24
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    As the authors explain beneath Table G.1, the next step is to call Solver and tell it to minimize H15 by changing C4:C5 subject to those constraints (if you feel it is necessary to include them to make sure the resulting parameters are reasonable). Are you familiar with Solver?

  25. #25
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    I have not used it before. This will be my first encounter with it.

  26. #26
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    After searching about solver online I have managed to come up with table G2. Kindly help me understand what the solver section does.

    See Attached:

    Solver.xlsx

  27. #27
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    Solver should be on the Data menu. Look there and make sure it is installed. Some Office setup programs consider Solver an optional part of the installation, so it may not have been included in your initial installation unless you specifically requested it. If it is not installed, then you should be able to install it from your setup program. I would note that the Solver dialog show in Figure G.1 is for the earlier (2007 and earlier) versions of Solver. 2010 and later will present a different dialog, though the same basic options should still be present.

  28. #28
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    Solver uses a numerical trial and error algorithm to find the values for C4 and C5 that make H15 as small as possible. In this case, H15 is how you are measuring "goodness of fit" -- how closely the values in E9:E14 match the values in D9:D14.

  29. #29
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    What do I do next? I realize that in table G3 there is an introduction or r between Phi and lambda.
    Last edited by gko_87; 05-23-2017 at 03:06 PM.

  30. #30
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    As far as Flynn and Francis's example problem, that is pretty much all there is to it. You have found the best estimate for phi and lambda for a zero inflated Poisson model for the sample data they used. At this point, I would suggest you do whatever you need to to make sure you understand the example.

    Once you are comfortable with the example, the next step will be figuring out how this should apply to your football data. What is going to be x in the football data? What are you "counting" for each x? Pull that data out of the source data, regress phi(s) and lambda(s) for your ZIP model, then apply that to your simulation.

  31. #31
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    Table G3 is a similar regression but using a zero inflated negative binomial model. Your OP only asked for a zero inflated Poisson model. If you want to be able to apply the procedure to other zero inflated models, then you can do as they have done. The overall procedure is the same. Set up the model using some arbitrary initial values for the zero inflated model, then call Solver to find the optimum parameters for that model. I had assumed that you only wanted the zero inflated Poisson and were not worried about programming the other zero inflated models.

  32. #32
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Could you please help me understand how tables G3 and G4 are derived and what they do? Are they not relevant. I really do not have any prior knowledge in statistics hence my asking for explanations every now and then. This appears to be a specialized area of statistics.

    If you refer to post #1, I used goal expectancy to compute the probabilities for 1X2,Over/Under and BTTs Yes/BTTs No. Can the same goal expectancy be used here? If yes how?
    Last edited by gko_87; 05-23-2017 at 03:25 PM.

  33. #33
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Sorry,I will really appreciate if you can take me through the other remaining two tables too.

  34. #34
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    Table G.3 is the final best fit (after setting up the spreadsheet and running Solver) for the zero inflated negative binomial model, which uses equation 2.4 instead of 2.1. Table G.4 is the same thing for the Hurdle model (equation 2.5). These spreadsheets should be essentially the same as your zero inflated Poisson spreadsheet, but you will use equation 2.4 in column 4 (E9:E15) of the ZINB spreadshet and equation 2.5 in column 4 (E9:E15) of the Hurdle spreadsheet.

    Then, as they discuss in the text, you can compare your "goodness of fit" measures (H15 and/or a chi-square statistic or whatever) to determine which model best fits the data. In this case, the ZIP model seems to fit the best.

    If you refer to post #1, I used goal expectancy to compute the probabilities for 1X2,Over/Under and BTTs Yes/BTTs No. Can the same goal expectancy be used here? If yes hoe?
    Your original spreadsheet does not explain how you came up with those goal expectancy values. I expect that you can use a similar procedure, using any of these zero inflated models to get a suitable value for "goal expectancy" (though in the ZIP model for example, there are two values that make up goal expectancy -- phi and lambda -- and not one). Then apply the new model in place of the POISSON() function.

    If I had to guess, I would guess that, for each team, you took "number of goals scored" (column 1 as x values) and how many times that team scored that many goals (column 2). Then figured out the Poisson parameter that best fits that distribution.

  35. #35
    Forum Contributor
    Join Date
    09-13-2016
    Location
    Kenya
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Creating a zero inflated poisson in excel

    Attached is a workbook showing how my data was derived in post #1. Please refer to England premier league results for 2013/2014 season:

    Initial Data.xlsx


    Note: Highlighted column is wrongly calculated in order to give results similar to the source data.

  36. #36
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Creating a zero inflated poisson in excel

    That calculation is specific to the regular Poisson distribution. You will need to figure out how to use that data to estimate phi and lambda for the ZIP model. I don't (yet) have any idea how to do that. It will almost certainly take some thought and testing and development to figure out how to get phi and lambda from that data.

    It looks like your current algorithm estimates the mean (lambda) of the Poisson distribution. I suspect that this part of the algorithm is about estimating the mean of the ZIP and using that information to get phi and lambda. Note that Flynn and Francis give formulas for the theoretical mean and variance for the ZIP just below equation 2.1. Those equations could be informative in figuring this part of the algorithm out.

  37. #37
    Registered User
    Join Date
    08-20-2019
    Location
    Mississauga, Canada
    MS-Off Ver
    2010
    Posts
    2

    Re: Creating a zero inflated poisson in excel

    Just curious, did this end up working out?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. in excel there are function of POISSON, how can we use in access?
    By isaacwel01 in forum Access Tables & Databases
    Replies: 4
    Last Post: 10-30-2015, 12:12 PM
  2. [SOLVED] Bivariate Poisson VBA
    By clattenburg cake in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-24-2015, 10:30 PM
  3. Inflated Header - How do I get rid of it?
    By escadi in forum Excel General
    Replies: 5
    Last Post: 03-06-2014, 08:28 AM
  4. [SOLVED] Poisson distribution
    By hnasir in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-19-2012, 03:47 PM
  5. Poisson Distribution--Help
    By neocube in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2011, 07:24 AM
  6. modelling bivariate zero-inflated poisson
    By ryusukekenji in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2009, 05:41 PM
  7. [SOLVED] File Size Inexplicably Inflated - 30 megs
    By Mark in forum Excel General
    Replies: 4
    Last Post: 04-14-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1