Excel LADZ
patreon
3 Excel Functions You Need To Know…
🕑 Added 2024-01-12 04:00:03 +0000 UTCG’day lads! In my experience, there are three important Excel functions that are important to learn! I’ll explain each one below, with an example, and how it’s useful in sports modelling.
- XLOOKUP
- POISSON.DIST
- BINOM.INV
JOIN the best Excel Sports Modelling Community by becoming an Official Member of the Excel LADZ Patreon! Receive downloadable access to every model as well as technical posts, like this, which will improve your understanding of sports modelling. You also have the ability to directly message me lads, so I can help you with any problems!
XLOOKUP
In my opinion, XLOOKUP should be one of the first functions an Excel user should learn! XLOOKUP has the capability to retrieve and cross-reference data across worksheets.
In the below example, I’m going to look up Chelsea’s Goals Scored in the 2022-23 EPL season.
Here, my Lookup Value is Chelsea (A25). I’m searching for Chelsea in the list of teams from B2:B21. Then, I’m returning Chelsea’s Goals Scored, which can be found in the range G2:G21.
XLOOKUP also allows us to pass through formulas within the function. For example, I can find the club with the most Goals Scored in the competition.
In this example, the MAX function finds the maximum number of goals in the range G2:G21. This is also the Lookup Array, and when matched to the corresponding value in the Return Array of B2:B21, the team Manchester City is returned.
POISSON.DIST Function
Put simply, the POISSON.DIST function returns the Poisson Distribution. It serves as a model for a rate that does not have a natural upper bound. As a result, it’s often used to predict the number of events that will occur during a given period of time.
In the example below, Everton are expected to score 1.9 goals in a match against Liverpool. The POISSON.DIST Function takes that 1.7 value as the mean (average) amount of goals scored in 90 minutes by Everton. Using the distribution, it computes the probability of exactly 0 goals occurring: 24.66%.
This can be used to calculate Everton’s probability of scoring 0 through to 20 goals in a match. The same can then be used for Liverpool. The probability of Everton scoring more goals than Liverpool can be compiled, which is done in the Excel LADZ Poisson Distribution Model: https://www.patreon.com/posts/predict-football-84701443
BINOM.INV Function
While the POISSON.DIST function can give you the probability of a certain number of events occurring, the BINOM.INV function allows you to simulate the number of events to occur. This is especially useful when running simulations for sports predictions.
In the example below, Steph Curry is projected to make 7.3 2 Point shots in a match. Using the BINOM.INV function, I can simulate a random point along the binomial distribution (where 7.3 is the mean, or expected value) to return Curry’s actual amount of shots taken in a match.
In the Poisson Distribution, there is no upper bound on the number of goals/points able to be scored in a match. Theoretically, a team can score 5, 10, 20 or even 100 goals/points (although this probably would obviously be miniscule) in a match. However, in the BINOM.INV function the first argument is the number of trials.
For this argument in the function, use a large number; I like to use 10,000. The next argument is the ‘probability of success’ for each of the 10,000 trials. This is just the expected value (A3) divided by the number of trials (10,000). To get a simulated value from Excel, use the RAND() function for the final argument in the function.
As the BINOM.INV function returns a simulated value for Curry’s 2P Shots, if I refresh the worksheet then this value may change.
I can also include other pieces of information for the BINOM.INV function to simulate. For example, now I learn that Curry makes 2P shots 60% of the time. This can be included in the BINOM.INV function so that Curry’s projected number of 2P Shots made can be simulated.
I’ve used the BINOM.INV function extensively to run the simulations for the Excel LADZ NBA Model: https://www.patreon.com/posts/2023-24-excel-93076132
Thank you for reading lads, make sure you JOIN the Excel LADZ Patreon to receive access to every model and improve your sports modelling skills!
Comments
Nic
Xlookup is 365, so no use if have older versions of Excel. Using Index/Match combo can achieve the same (at first might not be as nice looking and short as Xlookup, but more versatile than vlookup/hlookup if not using 365
Vasilis
I used to make my custom poisson random generator each time, i found out about inverse binomial from your video lad, cheers!
