RAND Function

What is the RAND Function?

The RAND function in Excel helps you generate random numbers between 0 and 1. Also called the random number function generates numbers greater than or equal to zero and less than or equal to 1.

In the newer versions of Excel, random numbers are generated using the Mersenne Twister algorithm.

As a financial analyst, you would be working on many numbers. However, even though these numbers mean something to the organization, they are still random to us.

So, we are working on random numbers, and sometimes, our analysis of these numbers might be wrong (everyone starts here!).

The only solution to getting good at number crunching is to do it 10 more than you usually do.

So, where does the RAND function come into this?

The function will allow you to create random datasets similar to what you work on, giving you a better idea of what needs to be done.

It would take a lot of time to individually fill out all the cells of a table with numbers. So instead, you can use this function to get results in seconds.

Random numbers also have applications in science, statistics, cryptography, and other fields.

In this article, you will see how you can return random numbers in spreadsheets and how you can make the best use of the function.

Understanding RAND function

The function falls under the category of Math and Trigonometric functions. It returns random numbers between 0 to 1. When you use the process in Excel, the result is a decimal, for example, 0.879481.

Hmm, that doesn't help us. I mean, who needs sample decimal numbers?

Let's say if you need random numbers between 0 to 50, can we return them with the help of the RAND function? We absolutely can!

Before we go on and see how to make the best use of the function, the most important thing to note is that RAND is a highly volatile function, which will refresh the value returned from the formula every time you open the spreadsheet or press F9. This means each instance in the entire workbook will recalculate.

To avoid the result from changing, you need to Paste Special the values, i.e., copy using Ctrl + C, press the keyboard keys Ctrl + Alt + V, and then select 'values.'

If you do this, the next time you open the spreadsheet or press the F9 key, the values will remain the same.

The Formula for RAND function

The syntax for the function is:

=RAND()

And that's it. Pretty simple, right? All you do is begin with an equals sign (=), type the function name, and put parentheses (round brackets). The function takes in no arguments inside the frames.

So. if you use the formula or syntax in any cell of the spreadsheet (here, cell C3), you will get a random number:

As stated earlier, since the function is quite volatile, whenever you press F9 in the cell or work on a different formula, the number will automatically change to an additional value between 0 and 1.

For example, 0.047704 becomes 0.527025 after clicking on the formula bar of cell B3 and hitting the Enter key.

In the following sections, we will see how to use the function through some examples, and the alternative, RANDBETWEEN, returns random integers between upper and lower limit values.

Examples Of RAND Function

So, we have already begun with a basic example that gives a random number in Excel.

But, as previously stated, random decimal numbers between 0 and 1 have a minimal application. We will see two examples of how we can use the function differently.

Example #1

Weight and height are some of a random population's most employed sampling parameters. So, let's assume you needed to create a random population for weight in Excel using the RAND function.

The list of people for which we have data is illustrated below:

Let's assume the heaviest person would weigh around 120 kg. That will be our upper limit for the data.

There is no lower limit for this example, so let's say the least someone could weigh is 1 kg. The formula to find the random weights will be =RAND()*120, which will give us the following results:

Woah! Too many digits after the decimal point. How do we deal with them? Pretty simple. All you need to do is use the round function and limit our numbers after the decimal to two values.

The formula becomes

=ROUND(RAND()*120,2)

which will give you the following result:

So, we multiplied the random function with our upper limit to get the random weights.

The random data generated will not consist of values equal to 120 kg. When you use the upper limit as 121kg, only then will the random data consist of importance equal to or less than 120.99 kg.

Example #2

We saw how you could create an upper limit for your randomly generated numbers, but what about its other half? Can we set up a lower limit as well as an upper limit?

Let's say that you need to determine the height for people, where the lower limit is 150cm, and the upper limit is 220 cm.

To find height with the upper and lower limit, you will use the formula =RAND()*(220-150)+150, which will give you the result illustrated below:

We multiplied the RAND() function with the difference between the upper and lower limit (220-150) and then added the lower limit to the formula to generate the random data set.

You can also generate random integers without the decimal digits by using the ROUND function and limiting the decimals to zero numbers.

Since RAND() is a volatile function, it's essential to paste the result as 'values.' So, copy the range of cells using Ctrl + C, press Ctrl + Alt +V, and select 'Values' from the Paste Special dialog box.

RAND vs. RANDBETWEEN

RANDBETWEEN is another volatile function that returns a random number, but it does so between specified upper and lower limits.

For example, if the upper and lower limits are 14 and 12, it could return all three numbers (including the boundaries) and anything in between.

The syntax for the RANDBETWEEN function is:

=RANDBETWEEN(bottom, top)

Where,

bottom = (required) smallest integer that the function will return

top = (required) largest integer that the procedure will produce

Let's assume you need to determine the age of a random population, with the upper and lower limits being 80 and 18, respectively. To do this, you will use the formula

=RANDBETWEEN(18,80).

After dragging down the formula to cell C12, you will get the following result:

An important thing to notice is that RANDBETWEEN gives more flexibility in returning a number within the desired range.

However, a drawback to the formula is that you cannot return decimal values, even if you use a similar input for top and bottom arguments.

So, even though both functions perform a similar task, there is quite a difference in how both work and the result they return.

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by James Fazeli-Sinaki | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• PEARSON Function
• PI Function
• SEARCH Function
• SQRT Function
• SUBTOTAL Function