# Help with automatic Poisson Distribution tables (1 Viewer)

#### emus

##### New member
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.

### { "lightbox_close": "Close", "lightbox_next": "Next", "lightbox_previous": "Previous", "lightbox_error": "The requested content cannot be loaded. Please try again later.", "lightbox_start_slideshow": "Start slideshow", "lightbox_stop_slideshow": "Stop slideshow", "lightbox_full_screen": "Full screen", "lightbox_thumbnails": "Thumbnails", "lightbox_download": "Download", "lightbox_share": "Share", "lightbox_zoom": "Zoom", "lightbox_new_window": "New window", "lightbox_toggle_sidebar": "Toggle sidebar" }

#### Attachments

• prb.zip
20.7 KB · Views: 76

#### June7

##### AWF VIP
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:

Replies
14
Views
469
Replies
27
Views
393
Replies
7
Views
740
Replies
4
Views
519
Replies
3
Views
255