Kill Time At Work With Recreational Math: Testing The Excel Random Function

There are a few prerequisites for this post:

1. You like recreational math.
2. You work at a computer and have access to Microsoft Excel.
3. You are bored out of your tree.
4. You don't want to get caught slacking off.

---

In this post I am going to show how you can test the quality of the random function in Microsoft Excel(pseudo-random function if you want to be pedantic).

We do this by plotting a random number against the next one in the sequence on a graph. If there are flaws in the generation of the random numbers they should show up as patterns of banding in the charts.

The example I am going to show will let you easily plot r_i against r_i+1, r_i against r_i+2, r_i against r_i+3, ... ,r_i against r_i+n

where n is some integer larger than 1.
r_i is some random number in the sequence.

Here is a sample screenshot:

Step 1

As you can see from the above figure the first thing is to set up a single column of random numbers in Column A.

Step 2

Step 2 uses the ADDRESS() function inside the INDIRECT() function to copy over the next random number in the sequence.

I have set it up to get the 2^nd row and 1^st column.
It gets the second row because there is a 2 in cell C1 and it gets the first column because there is a 1 in cell D1.
Cell E1 is not critical. I set it to 2 for no reason whatsoever and that tells the ADDRESS function to return a relative row and absolute column cell address.

Step 3

The cells in column C need to keep incrementing so that the x-y plot keeps plotting each random number against the one following it. This is done by simply adding 1 to the cell above it.

Step 4

Charting. If you know Excel then this is just a straightforward scatter plot of Column A on the x-axis and Column-B on the y-axis. Make the dots in the chart as small as possible.

Step 5

Start exploring.

1. Keep hitting the F9 key. This will cause the random function to refresh with new numbers. The chart pattern will change over and over and is entertaining.
2. Change the number in cell C1 from 2 to 3, then 4, then 5 and so on. This will let you plot a random number against the one that is 2 steps after it, 3 steps after it, 4 steps after and so on. Check to see if the random cloud has any patterns.
3. Make the spreadsheet LARGE. See how many rows you can get in the spreadsheet and points in the chart before your computer starts choking.

Closing Words

There is actually a serious mathematical side to this post. You are testing whether a function in a very popular application is of high quality.

I haven't found any patterns in the random plots yet. This new random function appears to be much better than the one from the 1990's when I first did this exercise.

Back then I found banding somewhere around r_i against r_i+33 if I remember correctly. That let me know that the Excel Rand() function back then had some major flaws in it.

Well, I hope this helps you to kill time at work while looking productive until the minute hand hits 5:00 pm and you can bugger off to the pub.

Thank you for reading my post.