Xem Nhiều 11/2022 #️ Generate Random Numbers In Excel / 2023 # Top 11 Trend | Trucbachconcert.com

Xem Nhiều 11/2022 # Generate Random Numbers In Excel / 2023 # Top 11 Trend

Cập nhật thông tin chi tiết về Generate Random Numbers In Excel / 2023 mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.

Excel has two very useful functions when it comes to generating random numbers. RAND and RANDBETWEEN.

Rand

The RAND function generates a random decimal number between 0 and 1.

1. Select cell A1.

2. Type RAND() and press Enter. The RAND function takes no arguments.

Note that cell A1 has changed. That is because random numbers change every time a cell on the sheet is calculated.

4. If you don’t want this, simply copy the random numbers and paste them as values.

5. Select cell C1 and look at the formula bar. This cell holds a value now and not the RAND function.

RandBetween

The RANDBETWEEN function generates a random whole number between two boundaries.

1. Select cell A1.

2. Type RANDBETWEEN(50,75) and press Enter.

3. If you want to generate random decimal numbers between 50 and 75, modify the RAND function as follows:

RandArray

If you have Excel 365, you can use the magic RANDARRAY function.

1. By default, the RANDARRAY function generates random decimal numbers between 0 and 1. The array below consists of 5 rows and 2 columns.

Note: this dynamic array function, entered into cell A1, fills multiple cells. Wow! This behavior in Excel 365 is called spilling.

2. The RANDARRAY function below generates an array of integers, 10 rows by 1 column, between 20 and 80.

Note: the Boolean TRUE (fifth argument) tells the RANDARRAY function to return an array of integers. Use FALSE to return an array of decimal numbers between 20 and 80.

How To Generate Random Numbers In Excel (A Step / 2023

There may be cases when you need to generate random numbers in Excel.

For example, to select random winners from a list or to get a random list of numbers for data analysis or to create random groups of students in class.

In this tutorial, you will learn how to generate random numbers in Excel (with and without repetitions).

Generate Random Numbers in Excel

There are two worksheet functions that are meant to generate random numbers in Excel: RAND and RANDBETWEEN.

RANDBETWEEN function would give you the random numbers, but there is a high possibility of repeats in the result.

RAND function is more likely to give you a result without repetitions. However, it only gives random numbers between 0 and 1. It can be used with RANK to generate unique random numbers in Excel (as shown later in this tutorial).

Generate Random Numbers using RANDBETWEEN function in Excel

Excel RANDBETWEEN function generates a set of integer random numbers between the two specified numbers.

RANDBETWEEN function takes two arguments – the Bottom value and the top value. It will give you an integer number between the two specified numbers only.

For example, suppose I want to generate 10 random numbers between 1 and 100.

Here are the steps to generate random numbers using RANDBETWEEN:

Select the cell in which you want to get the random numbers.

In the active cell, enter =RANDBETWEEN(1,100).

Hold the Control key and Press Enter.

This will instantly give me 10 random numbers in the selected cells.

While RANDBETWEEN makes it easy to get integers between the specified numbers, there is a high chance of repetition in the result.

For example, when I use the RANDBETWEEN function to get 10 random numbers and use the formula =RANDBETWEEN(1,10), it gives me a couple of duplicates.

If you’re OK with duplicates, RANDBETWEEN is the easiest way to generate random numbers in Excel.

Note that RANDBETWEEN is a volatile function and recalculates every time there is a change in the worksheet. To avoid getting the random numbers recalculate, again and again, convert the result of the formula to values.

Generate Unique Random Numbers using RAND and RANK function in Excel

I tested the RAND function multiple times and didn’t find duplicate values. But as a caution, I recommend you check for duplicate values when you use this function.

Suppose I want to generate 10 random numbers in Excel (without repeats).

Here are the steps to generate random numbers in Excel without repetition:

Select the cells in which you want to get the random numbers.

In the active cell, enter =RAND()

Hold the Control key and Press Enter.

Select all the cell (where you have the result of the RAND function) and convert it to values.

In the adjacent column, use the following formula: =RANK.EQ(A2,$A$2:$A$11)

Now you can use the values in column B as the random numbers.

Note: RAND is a volatile formula and would recalculate every time there is any change in the worksheet. Make sure you have converted all the RAND function results to values.

Caution: While I checked and didn’t find repetitions in the result of the RAND function, I still recommend you check once you have generated these numbers. You can use Conditional Formatting to highlight duplicates or use the Remove Duplicate option to get rid of it.

You May Also Like the Following Excel Tutorials:

How To Randomize A List In Excel: Sort Randomly Cells, Rows And Columns / 2023

The tutorial will teach you two quick ways to randomize in Excel: perform random sort with formulas and shuffle data by using a special tool.

Microsoft Excel provides a handful of different sorting options including ascending or descending order, by color or icon, as well as custom sort. However, it lacks one important feature – random sort. This functionality would come in handy in situations when you need to randomize data, say, for an unbiased assigning of tasks, allocation of shifts, or picking a lottery winner. This tutorial will teach you a couple of easy ways to do random sort in Excel.

How to randomize a list in Excel with a formula

Although there is no native function to perform random sort in Excel, there is a function to generate random numbers (Excel RAND function) and we are going to use it.

Assuming you have a list of names in column A, please follow these steps to randomize your list:

Insert a new column next to the list of names you want to randomize. If your dataset consists of a single column, skip this step.

In the first cell of the inserted column, enter the RAND formula: =RAND()

Either way, Excel automatically expands the selection and sorts the names in column A as well:

Tips & notes:

Excel RAND is a volatile function, meaning that new random numbers are generated every time the worksheet is recalculated. So, if you are not happy with how your list has been randomized, keep hitting the sort button until you get the desired result.

To prevent the random numbers from recalculating with every change you make to the worksheet, copy the random numbers, and then paste them as values by using the Paste Special feature. Or, simply delete the column with the RAND formula if you don’t need it any longer.

The same approach can be used to randomize multiple columns. To have it done, place two or more columns side by side so that the columns are contiguous, and then perform the above steps.

How to shuffle data in Excel with Ultimate Suite

If you don’t have time to fiddle with formulas, use the Shuffle Cells tool included in our Ultimate Suite for Excel to do a random sort faster.

The Shuffle pane will appear on the left side of your workbook. You select the range where you want to shuffle data, and then choose one of the following options:

Cells in each row – shuffle cells in each row individually.

Cells in each column – randomly sort cells in each column.

Entire rows – shuffle rows in the selected range.

Entire columns – randomize the order of columns in the range.

All cells in the range – randomize all cells in the selected range.

In this example, we need to shuffle cells in column A, so we go with the third option:

And voilà, our list of names is randomized in no time:

If you are curious to try this and explore a lot more fascinating features included with Ultimate Suite for Excel, you are welcome to download a 14-day trial version.

You may also be interested in

Excel Formula: Random List Of Names / 2023

At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this:

=

INDEX

(

names

,

5

)

However, the trick in this case is that we don’t want a single name at a known location, we want 10 random names at unknown locations between 1 and 100. This is an excellent use case for the RANDARRAY function, which can create a random set of integers in a given range. Working from the inside out, we use RANDARRAY to get 10 random numbers between 1 and 100 like this:

RANDARRAY

(

10

,

1

,

1

,

COUNTA

(

names

)

The COUNTA function is used to get a dynamic count of names in the list, but we could replace COUNTA with a hardcoded 100 in this case with the same result:

=

INDEX

(

names

,

RANDARRAY

(

10

,

1

,

1

,

100

,

TRUE

))

In either case, RANDARRAY will return 10 numbers in an array that looks like this:

{

64

;

74

;

13

;

74

;

96

;

65

;

5

;

73

;

84

;

85

}

Note: these numbers are random only and do not map directly to the example shown.

This array is returned directly to the INDEX function as the row argument:

=

INDEX

(

names

,

{

64

;

74

;

13

;

74

;

96

;

65

;

5

;

73

;

84

;

85

}

Because we are giving INDEX 10 row numbers, it will 10 results, each corresponding to a name at the given position. The 10 random names are returned in a spill range beginning in cell D5.

Prevent duplicates

One problem with the above formula (depending on your needs) is that RANDARRAY will sometimes generate duplicate numbers. In other words, there is no guarantee that RANDARRAY will return 10 unique numbers.

To ensure 10 different names from the list, you can adapt the formula to randomly sort the full list of names, then retrieve the first 10 names from the list. The formula in F5 uses this approach:

=

INDEX

(

SORTBY

(

names

,

RANDARRAY

(

COUNTA

(

names

))),

SEQUENCE

(

10

))

The approach here is the same as above – we are using INDEX to retrieve 10 values from the list of names. However, in this version of the formula, we are sorting the list of names randomly before handing giving the list to INDEX like this:

SORTBY

(

names

,

RANDARRAY

(

COUNTA

(

names

)))

Here, the SORTBY function is used to sort the list of names randomly with an array values created by the RANDARRAY function, as explained in more detail here.

Finally, we need to retrieve 10 values. Because we already have names in a random order, we can simply request the first 10 with an array created by the SEQUENCE function like this:

SEQUENCE

(

10

)

SEQUENCE builds an array of sequential numbers:

{

1

;

2

;

3

;

4

;

5

;

6

;

7

;

8

;

9

;

10

}

which is returned to the INDEX function as the row argument. INDEX then returns the first 10 names in a spill range like the original formula.

Bạn đang xem bài viết Generate Random Numbers In Excel / 2023 trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!