wilhelmgras
New Member
- Joined
- Jul 27, 2017
- Messages
- 3
At the moment I am trying to predict a league table, based on the results of all (n=306) the individual games. For each game, I have (in Sheet 1) an expected goals value for the home team (G2:G307) and away team (H2:H307).
In Sheet 2, I have a 11x11 poisson distribution to calculate the odds for individual games. Cells A9-A19 and B8-L8 have the values 0-10 in them. Cell B9 (top left corner of distribution) has the following formula: =POISSON.DIST($A9,$P$2,0)*POISSON.DIST(B$8,$P$3,0) where cell P2 has the expected home goals (from Sheet 1), and P3 the expected away goals. The other cells of the distribution are the same with A9/B8 updated. After entering those data from Sheet 1 into P2 and P3, I use a simple summation to calculate the chance of a home win(O9)/draw(O10)/away win(O11), which I subsequently copy back to Sheet 1 (where I intend to do a MC simulation when I have all the predictions).
My question is this: how can I automate copying all 306 expected goal sets from Sheet 1 to cells P2 and P3 in Sheet 2, and then copy back the values in O9-O11 to sheet 1? As I intend to update my league prediction every week, I would like to avoid manually copying everything back and forth.
A direct answer would be fantastic, although I am also happy with links to instructions from where I should be able to figure it out by myself. I know of the existence of Macro's, but recording one didn't work out for me.
Thanks in advance
In Sheet 2, I have a 11x11 poisson distribution to calculate the odds for individual games. Cells A9-A19 and B8-L8 have the values 0-10 in them. Cell B9 (top left corner of distribution) has the following formula: =POISSON.DIST($A9,$P$2,0)*POISSON.DIST(B$8,$P$3,0) where cell P2 has the expected home goals (from Sheet 1), and P3 the expected away goals. The other cells of the distribution are the same with A9/B8 updated. After entering those data from Sheet 1 into P2 and P3, I use a simple summation to calculate the chance of a home win(O9)/draw(O10)/away win(O11), which I subsequently copy back to Sheet 1 (where I intend to do a MC simulation when I have all the predictions).
My question is this: how can I automate copying all 306 expected goal sets from Sheet 1 to cells P2 and P3 in Sheet 2, and then copy back the values in O9-O11 to sheet 1? As I intend to update my league prediction every week, I would like to avoid manually copying everything back and forth.
A direct answer would be fantastic, although I am also happy with links to instructions from where I should be able to figure it out by myself. I know of the existence of Macro's, but recording one didn't work out for me.
Thanks in advance
