Three practical Random functions in Excel: RAND, RANDBETWEEN and RANDARRAY

Hi, I am the uncle Mr Zhao who prefers function formulas and likes to use Excel charts to manage warehouses.

Some time ago, a small partner asked a question about random values: how to generate a random value in the range from-2.5 to + 2.5?

Here are a few examples to talk about three random functions ↓ that are often used in Excel.

1, RAND function in fact, the above problem only needs a RAND function to solve!

Select any cell and enter the formula in the edit bar:

= RAND () * 5-2.5

I'm not lying to you. It's easy!

The RAND function is a function with no arguments: RAND () returns a random real number greater than or equal to 0 and less than 1.

If you want to generate a random number between an and b, use:

= RAND () * (bmura) + a

For example, if you want to generate random numbers in the range of-5 to 8, you can add axiomatic color 5 # # 5 # # 8 to the formula as follows:

= RAND () * 13-5

After learning this formula, no matter how many problems of the same type are encountered in the future, they can be easily solved.

2. RANDBETWEEN function. Let's talk about RANDBETWEEN function again. For example, to construct a list of random values between the ages of 18 and 60, the effect is shown in column B of figure below:

At this point, we can use the RANDBETWEEN function.

Enter the formula in the B3 cell, and then drop down to copy the fill formula:

= RANDBETWEEN (185.60)

The RANDBETWEEN function is used to generate a random integer between two specified numbers. The syntax is:

= RANDBETWEEN (minimum integer, maximum integer)

RANDBETWEEN (18Bol 60) generates a random integer between 18 and 60.

Of course, the RANDBETWEEN function can also be used in nesting with other functions to generate text randomly.

For example, randomly generate a list of gender text values, as shown in the following figure, enter the formula in the C3 cell and drag the fill down:

= CHOOSE (RANDBETWEEN (1) 2), "male", "female")

Briefly explain the formula:

First, RANDBETWEEN (1 and 2) returns a random integer between 1 and 2.

Then use the CHOOSE function to select the corresponding text content according to this value:

Returns "male" if the integer is 1, and "female" if it is 2.

Thus, the RANDBETWEEN function is very useful for quickly generating test data!

3. The RANDARRAY function finally uses an example to talk about the RANDARRAY function. As shown in the following figure, how to randomly disrupt the order of the list in column B, and the effect is like column D.

Here we will use the RANDARRAY function to enter the formula in the D3 cell:

= SORTBY (B3VOR B13MA RANDARRAY (COUNTA (B3:B13))

Formula parsing:

First, use the COUNTA function to calculate the number of text in the cell range B3:B13, that is, the number of people on the list.

Then use the RANDARRAY function to generate a list of unrepeatable random values based on the number of people.

Finally, the SORTBY function is used to sort the list area B3:B13 according to this list of random values.

Thus, the biggest breakthrough of the RANDARRAY function is that it can generate a set of random values that are not repeated.

All right, that's all for today.

4. Finally, in Excel, there are three random functions:

RAND function: returns a random value greater than or equal to 0 and less than 1

RANDBETWEEN function: this function returns a random integer within a specified range

RANDARRAY function: can generate a set of random values.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang