What the simulation does
Open file 1-Gambling.xlsx on sheet “Frequencies.” This time we cast two dice at once and sum the number of eyes in column C; we repeat this process 9 more times. In column F, we calculate how often we had a hit of 2 eyes in total, 3 eyes, and so on, up to 12 eyes.
The frequencies are plotted in a graph. Cell F14 calculates the average of column C. It turns color for extreme values. The average is also plotted in the graph as a vertical line—based on the two sets of coordinates shown in E16:F17.
The curve keeps changing each time we hit Shift F9. Very rarely does it come close to a normal distribution with a mean somewhere in the center. The chance for this to happen would increase if we would have used more dice and more repeats.
What you need to know
On order to calculate frequencies, we need the function FREQUENCY. This is a so-called array function (more on this in Simulations 62 and 63). Such functions return an array or require an array for intermediate calculations. All array functions have to be implemented with three keys at the same time: Ctr Sh Enter.
The function FREQUENCY returns an array of multiple answers based on a set of “bins.” In this case, the bins are in column E. The function “reads” the bins as follows: 2 covers all cases up to and including 2, 3 covers all cases >2 and <=3, etc. To make this function work, you need to select all the cells that are going to hold the frequency values all at once, before you use the array function. Once the array function has been implemented with Ctr Sh Enter, you will see the formula in the formula bar surrounded by braces—like this: {=FREQUENCY(…,…)}. Do not type the braces; they come automatically with Ctr Sh Enter.
Changing colors of cells under certain conditions is done with so-called conditional formatting (located under the Home tab). When the specified conditions kick in, the cell will be formatted according to certain settings. In our case, we want to flag averages under 5.5 and over 8.5, which requires an OR function.
What you need to do
- Column C sums the eyes of both dice.
- Select the entire range F2:F12 first, before you implement =FREQUENCY(C2:C11,E2:E12), and then hit Ctr Sh Enter. Notice the braces in the formula bar.
- Type in cell F14: =AVERAGE(C2:C11).
- Select cell F14, Home | Conditional Formatting | New Rule | Use a Formula | =OR($F$14<5.5,$F$14>8.5) | Format color.
- The average line in the graph is based on a new series of values with two sets of coordinates: E16:E17 for the X-values, and F16:F17 for the Y-values.
- Hit Sh F9 for new simulations. As they say, “Results may vary” (see below).
