Head to head win % fantasy football

[Kurtisknox]

Hey guys, I've been trying to find somewhere where I can plug in my fantasy team and my opponents team and calculate what the likelihood of me winning is, based off floors and ceilings of a player. I can pretty easily find floors and ceilings, I just don't know how to make excel work for me to calculate win percentages. I've looked through a couple pages on the forums but I don't see anywhere that discusses my needs. Any ideas?

 

[Oaktree]

Welcome to the board.

Sounds like a good time for simulation. Set each player's points to be a random number between their floor and ceiling, sum the total for each team, and determine who wins. Then, use a macro to repeat that process a large number (10,000?) times. With a large enough number of simulations, the simulated win percentage will converge on the true win percentage.

 

[Kurtisknox]

Awesome! Now, how do I do that? Haha

 

[Oaktree]

I'll nudge you in the right direction...

Say you only have one guy, Jordan Howard (Go Bears!) and he has a floor of 5.5 points (cell D3) and a ceiling of 20 points (cell E3).

Set F3 = RANDBETWEEN(D3*10,E3*10)/10

The 10s are there because RANDBETWEEN only likes integers.

Then, every time you press F9 to recalculate, Excel will randomly generate a new score for Jordan.

To simulate that 10,000 times, you could use a simple macro like:

Sub Simulate()

Dim i As Integer

For i = 1 To 10000
    
    ActiveSheet.Calculate
    
    Cells(i, 10) = Range("F3")

Next i

End Sub

Once you run that, the macro will put simulated scores for Jordan in cells J1:J10000

Now, you'd just have to set your sheet up with scores for each person on each team and a summed total. Instead of simulating cell F3, you'll be simulating whatever each team score is (or just the "who the winner was" calc if you want). Use a similar macro to log the results.

 

[Kurtisknox]

Awesome. I have my team and my opponent set up on the same sheet, and I have the sums underneath the random numbers together. I have figured out where the Macro button is, but I am still unable to get it where I want it to go. Basically I'd prefer if the simulation numbers ran on a separate sheet, and then off that data formed a percentage underneath each team on the original sheet.

 

[Kurtisknox]

Okay after playing around with it, I have come to an error. I did what you said and summed the team, then copied/pasted the code, except I changed F3 to F11 since thats the cell that the sum is in. The debugger comes up and Cells(i, 10) = Range("F11") comes up as an error. What am I doing wrong?

 

[Oaktree]

That line should be ok. Can you please post the full code you're using?