28 Aug
0

Excel and VBA will always be a great help to your Daily Fantasy Football Lineups. Today, you will not be disappointed once again. Your fantasy football projections are key components to provide you an expectation of performance for a NFL slates. If you have played Daily Fantasy Sports Football for any amount of time, you already know some websites provide better projections than other websites. When you in the selection phase of your Fantasy Football team, you want to eliminate any player you think will not meet or exceed his fantasy football projections.

With the use of Monte Carlo Simulation in Excel, you can determine the probability of an outcome by simulating a situation repeatedly. If you wanted see how likely a quarterback will pass for his average in passing yards against a defense, you would use the Monte Carlo Simulation. You use Excel to run x amount simulations for a game situation. For example, you could determine the probability of Tom Brady exceeding his passing yard average while playing against the Kansas City Chiefs’ defense by running 1000 simulations.

When you use several variables in your Monte Carlo Simulation, you will get a much better picture of what to expect from a player. For the example above, the Monte Carlo simulation considers the strength of a NFL defense and the passing yards of the NFL quarterback. You can use as many variables, but the reality is you will never be able to model every factor that affects a NFL Football game. With the added variables, you add more complexity to the code. The example above is simple straightforward and can be done for each stat as long as you know the average and standard deviations of the variables.

The use of Excel functions such as RAND and RANDBETWEEN allows you to produce random outcomes of your variables. The other Excel function you will use is the NORM.INV. The Monte Carlo Simulation will use the functions mentioned, but you need to look at the video to learn the hack that instructs Excel to simulate a game situation a 1000 or more times. It involves the What-IF analysis found under the Data tab. Under the What-IF Analysis, you would use the Data Table function to benefit from the use of the Monte Carlo simulation.

Monte Carlo simulation is actually the begins the process to determine how much of chance a player like Tom Brady has to reach his fantasy football projections for passing yards against a Kansas City Chiefs’ defense. After you run 1000 or more simulations, you need to breakout your Math hat. Yes, you have to do some math. You may want to find the average of the random out comes. You may want to find the standard deviation for the outcomes from the Monte Carlo simulation. The whole purpose of executing several simulations is to determine a probability of outcomes actually happens.

In the example below is an Excel formula, you can determine the probability of the player not reaching his passing yards average against the defense.

=COUNTIF(AJ6:AJ10005,”<233.33")/AF5
The cell range AJ6:AJ10005 contains outcomes of your simulation. It is also the criteria range for the COUNTIF function. The criteria, "<233.33", counts only the outcome lower than player’s season average of 233.33 yards. The cell AF5 is the amount of outcomes. You would use divide the result of COUNTIF by the amount in cell AF5 to determine the probability of the player passing below his average against the defense.

You see the importance of Monte Carlo Simulation can have for the research and selection phase for your Daily Fantasy Sports Football lineups. It will help you trust your fantasy Football projectiion You can create better lineups and help you like the use of Excel and VBA with Fantasy Football little more.