2

I'm trying to use Excel to determine the outcomes of hockey games. In the game that I'm simulating, I have each team has a goals for and standard deviation which I call in this function: (NORM.INV(RAND(), D2, E3)) and (NORM.INV(RAND(), D3, E2)) where column D is the goals for and column E is the standard deviation calculated from each game of interval (78 games). I want to make the NORM.INV run 10k times so I can aggregate the outcome of each toy game.

I made two columns (Flames, Ducks) and in the first cell below each team I typed in the NORM function. When I try to autofill the subsequent columns, Excel seems to think it is a series and the columns increment D2-->D3 etc. I have tried changing the options to copy cells from fill series and it still does the same thing.

I've searched google and youtube but I can't find a way to make the function run a given number of times and I can't copy paste. Please help.

Franck Dernoncourt
  • 5,862
  • 12
  • 44
  • 80
user183910
  • 21
  • 2

1 Answers1

3

I first tried this approach via python, just to check it out on a random dataset, hopefully it works directly on excel too --

To prevent Excel from auto-incrementing cell references when dragging your formula:

  1. Use absolute references ($D$2, $E$2):

    =NORM.INV(RAND(), $D$2, $E$2)  // Flames (drag down)
=NORM.INV(RAND(), $D$3, $E$3)  // Ducks (drag down)

  2. For 10k+ simulations, copy the formulas, then Paste Special → Values (Ctrl + Alt + V → V) to fix results.

Aggregate outcomes with:

=COUNTIF(B:B, ">"&C:C)  // Wins  
=COUNTIF(C:C, ">"&B:B)  // Losses

(No volatile RAND() recalculations, no reference shifts.)

Pratyaksh Patel
  • 87
  • 3