simulation to pick from normal distribution

I'm not sure I understand the question. When I open your file, I can press F9, which generates new random numbers for I4:I7 and in D15:D58. I don't know what you mean by "refreshing" the page or what constitutes a single simulation, but it looks like it is working just fine to me. I am not familiar with office 365, so maybe there is something different in the way 365 handles these volatile functions (I am not aware of any differences)? Or perhaps it is just knowing how to trigger Excel to recalculate the worksheet (because the RAND() function should generate a new number every time the worksheet is calculated)?

L4:L8 all contain the formula =I4, so I am not at all surprised that they all contain the same number. What I don't understand is, if you want 5 different random numbers, why you don't have =NORMINV(RAND(),$E$4,$A$6) in L4:L8? That's what I would have expected. Or maybe =NORMINV(RAND(),$E4,$A$6) in L4, copy across to P4 (to get 5 simulations for dave), then copy down to get 5 random numbers for each of dom, Eric, and Elias. In the end, if you want 5 copies of the same random number, then you use something like =I4 like you have now. If you want 5 different random numbers, you have a separate RAND() function for each random number.

I am not sure what you intend to do. Here's what I see (if you are unfamiliar with the evaluate formula tool, you might follow along in your own copy of Excel: https://support.office.com/en-us/art...6-a70aa409b8a7 ):

=INDEX(
range argument for INDEX() (looks okay): PointsTable[[wins pts]:[loses r5]],
row# argument (looks okay): MATCH(C$1,PointsTable[name],0),
column# argument (looks okay here, but problem in sheet1): MATCH(B2,OFFSET(HelperProb[[#Headers],[winspts]],MATCH(C$1,HelperProb[name],0),0,1,12)))

add the same small fraction to each entry for some reason: +0.000001*COLUMN()

The problem I see with the column# argument is that, for dave and dom, the lookup range returned by the OFFSET() function (AJ4:AU4) is all 0's. The MATCH() function here is set to use a binary search. The random number being used for the lookup value will always (or almost always) be greater than 0, so this lookup always returns the last column # in the search range. So, for dave and dom, this will always return the same value from the range argument. I'm not sure what you intend to be happening here, but it looks like you need to figure out why sheet1!AJ4:AU4 is all 0s rather than a set of increasing values (looks like it should go from 0 to 1).

The final part of the function, that adds a small fraction to each entry, seems to be an attempt to make sure each entry is unique. Your source table only has 12 values, so it is inevitable that you will get some duplicate values returned from the table. However, for a given name, the column is the same all the way down, so this will always add the same fraction. I wonder if you intended to use ROW() instead of column so that this will add a unique value to each entry under a given name.

I have not gone through it in more detail than that.

I am not sure what you are trying to do here. The algorithm as I see it right now:

1) Make a collection of 12 random numbers for each name on sheet1 -- along with some other stuff necessary for selecting one of these 12 random numbers.
2) On sheet 2, feed a random number into a lookup function to randomly return one of the 12 random numbers computed on sheet1.
3) Repeat step 2 multiple times to perform the simulation. Of course, with only 12 random numbers to choose from, it is inevitable that there will be some duplicate results.

At this point I am not sure what to recommend, because I am not sure what is supposed to be happening between sheet1 and sheet2. Clearly the random numbers on sheet 1 are supposed to figure into the random numbers generated on sheet2, but it is not clear to me what you intend. It seems clear that "randomly choose one of the 12 numbers" is not what you want. If you can help us understand what you are trying to do when you generate a random number on sheet2 from the random numbers in sheet1, we should be able to help you program that into Excel.

On edit: After a little more looking, is step 2 supposed to be:
2a) Randomly choose a mean from the 12 means in row 21 (for dave) of sheet1.
2b) Choose a random normal from that randomly chosen mean and a standard deviation of 10 (=NORMINV($B2,formula to randomly choose mean similar to current formula,10)
Trying this in your sheet, I get all unique numbers for dave in sheet2 column2. Your number formatting is set to display to the nearest integer, so it looks like some of the numbers are duplicates, but they are not exactly the same number.

Try this:

1) In sheet1, copy E21:P30 and paste over E4:P13
2) In sheet2, edit C2 to make this a random normal based on returned mean: =NORMINV(B2,current formula in C2,10). If desired use a cell reference instead of the hardcoded 10 for the standard deviation argument.
3) copy C2 and paste over the rest of column C.
4) Evaluate results to see if this is what you are trying to do with dave's column.
5) If that works closer to correctly, then I would expect dom's column in sheet2 to be the same change =NORMINV(B2,current formula in D2,10).

Is that getting closer?

I am glad that is working out.