Random Numbers and more in Excel

Office-Watch.comLast updated: 10 May 2021

Microsoft 365,Microsoft Excel,Microsoft Office,Office 2003,Office 2007,Office 2010,Office 2013,Office 2016,Office 2019,Office 2021 / Office LTSC,Office 365

About the RAND() function to make random numbers in Excel plus related functions RandBetween() and RandArray() plus some tricks for getting whole numbers only and even rolling ‘dice’ made in Excel.

By Michael Barden

Typing the =rand() function into a cell in an Excel spreadsheet will return a random number that is greater than or equal to 0 and strictly less than 1, with up to 9 numbers after the decimal place (e.g. 0.037595172 or 0.999999999).

If you then press F9, a new random number will take the place of the old one. This is because F9 re-calculates the formulas on all open worksheets based on the current data, including all random numbers.

Ok… We Really Mean “Pseudo-Random” Numbers

The =rand() function in Excel cannot be easily analyzed to produce a predictable pattern, but it is not a truly random process. To be perfectly correct, the numbers generated are in fact approximations of actual random numbers known as “pseudo-random” numbers.

Computers are by definition “deterministic” – when given a particular input, they will always produce the same correct output. This is an inherent problem when it comes to producing a purely random output, and so algorithms have been developed to simulate random behavior.

For people who don’t rely on a heavy use of random numbers, the =rand() function in Excel should be more than enough.

In some heavy-use situations however, it won’t. If you are serious about your need for a very good random number generator, then you may want to look at a number of third-party alternatives.

Note that the =rand() function does not try to avoid repeating a number.

With that said, Excel 2003 had an upgraded random number generator over the previous versions of Excel (whose random number generators were simply not sufficiently random).

Naturally, this change introduced a bug that allowed for negative random numbers! We will talk about this bug below.

Random numbers between a range of values

So far we know about random numbers between 0 and 1, but in many cases we want larger random numbers. If you want to increase the range to find numbers between 0 and 50, use the =rand()*50 formula (where the * character represents the multiplication sign). This acts to “scale” the random numbers between a range of 0 and 49.999999999.

What if you want a random number in a certain range – say between 20 and 70? To do this, =rand()*(y-x)+x where x is the smallest number in the range and y is the largest number in the range. In our example, x=20 and y=70.

When you plug in the values to our formula it becomes =rand()*(70-20)+20, which in turn becomes =rand()*(50) + 20. We know from above that =rand()*50 will produce a random number between 0 and 50. If we then add 20 to the result, all we are doing is “shifting” the range of possible numbers by 20. Hence, it produces a random number between 20 and 70 (or 20 and 69.999999999 if you want to be more precise).

Whole Number only

If you only want whole numbers (integers), then you can combine the rand() and int() functions to obtain only the integer part of the random number. For example, typing in =int(rand()*100) would generate a random integer between 0 and 99 inclusive.

Similarly if you wanted whole numbers between 1 and 100 inclusive, then simply shift the range by adding 1 as follows: =int(rand()*100) + 1.

If you want some assurance that your formula is working correctly, copy and paste it into a number of cells and press F9 a number of times to observe that the results are always within the desired range.

RandBetween()

Mathematics I hear you say? I don’t want to have to know mathematics! After all, it’s easy to make a mistake trying to scale and shift your random numbers.

Luckily there is an easier way of creating a random whole-number within a certain range in Excel. This is done by using the =randbetween(bottom,top) function where “bottom” is the smallest integer the function will return, and “top” is the largest integer the function will return. For example, =randbetween(1,100) will produce random integers between 1 and 100 inclusive.

Similarly if you wanted a range of positive and negative values, you could use something like =randbetween(-100,100).

Note: unlike RAND() – RANDBETWEEN() returns integer values only with no fraction. Some documentation is unclear on this point, talking about ‘numbers’ instead of ‘integers’

If you want a random number with fractions in a certain range you need to use RAND() in a formula.

Random roll of the dice

To emulate the roll of a standard dice use =int(rand()*6) + 1

For a ‘Dungeons and Dragons’ ten-sided dice (0 to 9) use =int(rand()*9) in two cells – one for each dice.

Lot’s of Random Numbers

Excel 365 can produce many random numbers from a single RandArray() formula with options to set a minimum-maximum range plus integers only.

The #Name Error and Fix

If you try to test the =randbetween(bottom,top) function in Excel 2003 and it is not available, it will return a #NAME? error.

To fix this you need to install and load the “Analysis ToolPak” add-in, by first navigating to “Tools | Add-Ins” and then selecting the “Analysis ToolPak” box from the list provided. Finally clicking “OK” to install the add-in.

In Excel 2007 this somewhat pointless distinction between functions is removed – any functions from the old ‘Analysis Toolpak’ are included in Excel 2007 by default.

Negative Random Numbers in Excel 2003

As mentioned above, Microsoft’s attempt to improve the =rand() function for Excel 2003 was originally flawed as it unwittingly introduced occasional negative random numbers.

Office Watch broke the news of the Excel 2003 RAND() bug at the time.

I love this quote from that issue:

“The company says that ‘The new generator passes all standard tests of randomness.’ which is great but a pity it wasn’t tested for plain and simple accuracy.”

Ouch …

This problem was fixed in the Microsoft Excel 2003 Hotfix Package , so unless you are running an un-patched version of Excel 2003, you can be assured that your random numbers will always be positive.