Help with automatic Poisson Distribution tables (1 Viewer)

emus

New member
Local time
Today, 21:44
Joined
Nov 17, 2021
Messages
8
Hello,

For a school project, I am calculating the results of the 2020-2021 EPL season with the results from the 2019-2020 season using Poisson Distribution.
The way I do this is,
I calculated the mean (λ) for away matches and home matches since they differ. I have made a table as such, but I need to automate this table, since there are 20 teams and they all play each other 2 times in a season. I also need to sums in the right (the draw, liverpool win, man city win) to be automated since that is what I will use to decide what is the most probable outcome. I think this is only possible with a macro, I don't think of any way I could make a huge table and pull the formula to the side or something. But the problem is, I don't know coding or anything making making macros, so I'm asking for your help right now. The file is in included in the post so you can view the entire thing. Thanks in advance.



resim_2022-05-14_223550153.png

 

Attachments

  • prb.zip
    20.7 KB · Views: 76

June7

AWF VIP
Local time
Today, 10:44
Joined
Mar 9, 2014
Messages
4,451
So you want to adjust that table probability inputs depending on whichever 2 teams are specified?
Use a matrix lookup formula. Review Matrix Lookup

For a start, formula in cell B52: =POISSON.DIST(A52,INDEX($A$1:$U$21,MATCH($B$50,$A$1:$A$21,0),MATCH($A$51,$A$1:$U$1,0)),FALSE)
and in C51: =POISSON.DIST(C50,INDEX($A$24:$U$44,MATCH($A$51,$A$24:$A$44,0),MATCH($B$50,$A$24:$U$24,0)),FALSE)
in J51: =A51 & " Win"
in J52: =B50 & " Win"
 
Last edited:

Users who are viewing this thread

Top Bottom