23.2 Random Numbers

On the RAND tab of the workbook, there is a list of a thousand numbers generated. To the right of the list of a thousand numbers are a PivotTable and a PivotChart. I will use this setup to explain random numbers and normal distributions in an example format by recreating what is already there.

The first step is to create a list of a thousand random numbers. We will select a thousand different rows. Selecting a thousand cells is simple and easy to repeat once you understand how to do it. Hit F5 to bring up the Go To dialog and type in what you want to reference as your selection. Or we can go to the Name box and type in the selection size. This second option is the preferred method. For example, we would activate cell M3, then go to the Name box to the left of the formula bar and type M3:M1002. Once the cells are selected, we need to fill them, which we can easily do all at one time by pressing Ctrl+Enter. Before pressing Ctrl+Enter, type the formula into the first cell (M3), which should be white (all below are gray) with the green border surrounding it and everything below in column M as far as you can see. Type the formula =RANDBETWEEN (-30,30) and then press Ctrl+Enter, and every selected cell will reflect the same formula. If we break down this formula, we note that the name of the function is explanatory. The function will select a random number in between the range given in parentheses. So, each cell in the selection of a thousand numbers will have a random integer number from -30 to 30. We can also add a title by putting “Random” into cell M2. We can then analyze that data.

Here in the example, we have created a PivotTable to see the distribution of numbers. As you can see, the interval (group) is every five integers, and the result is the count of the numbers in the data labeled “Random.” The count of these numbers centers around 80 for each interval of five, which can be further approximated by looking at the chart to the right with intervals on the horizontal axis and count on the vertical axis. Mathematically, we would expect each interval to be around 83 because 1,000 (number of data points) divided by 12 (number of intervals) is 83.3333, so our eyes-only guess of 80 makes sense.

In the next section, you can create your own PivotTable and PivotChart to further practice those two things, aided by a detailed description of grouping and making the table and chart. Another interesting thing is that you can go to PivotTable Analyze and refresh after clicking on the PivotTable, and all the numbers will change. This occurs because the data is a formula based on random numbers that recalculates when prompted. It can also be helpful to note that there is one extra value included in the last interval, so it has six values instead of five.