Cara menggunakan excel randomizer template

Tips ini bukanlah hal baru, namun mungkin saja dapat berguna bagi anda yang kebetulan belum pernah melakukan pemilihan data random dengan Excel.

Kasus : Seorang peneliti ingin memilih secara random 5 dari 30 SMA yang tersebar di 4 Kabupaten/Kota.

Penyelesaian :

Step 1 : Urutkan data SMA seperti tampilan berikut ini :

Step 2 : Letakkan Kursor di Cell B2, lalu ketik “=rand()”. Kemudian copy formula sampai B31

Step 3 : Letakkan kursor di Cell C2, kemudian ketik “=INDEX($A$2:$A$31,RANK(B2,$B$2:$B$31))”. Karena akan dipilih 5 data random, maka Copy formula sampai ke cell C6

Dari hasil pemilihan random sampling, terlihat bahwa 5 SMA yang terpilih adalah SMA28, SMA13, SMA1, SMA24 dan SMA4

What is a random sample, you may be asking, and why would you need one? Well let’s say, for example, that you want to survey your customers to understand how you’re meeting their needs, and how you could do better. Your whole customer base is a population. You’re obviously not going to survey every single person in this population. Maybe you don’t have all of their contact information. Maybe they won’t all reply to your survey. Whatever the reason, the group of customers you have the ability to contact with your survey is your sampling frame. And the people you actually contact with your survey and who actually fill it out are your sample. You want to make sure your sample is randomly selected (hence, a random sample) to make sure that everyone in your sampling frame has an equal chance of being selected.

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:

• Automatically Sort Data in Alphabetical Order using Formula.
• Random Group Generator Template
• How to Shuffle a List of Items/Names in Excel?
• How to Do Factorial (!) in Excel (FACT function)

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Name

Email

YES - SEND ME THE EBOOK

3 thoughts on “How to Generate Random Numbers in Excel”

1. Najm Alghazali

   The link or the Ebooks do not send to my email

2. Daniel H

   Just a trick, RAND and RANDBETWEEN are volatile functions, so each time there’s a change they will be updated. To avoid this, you can create a Pivot Table referencing the cells with the random values (functions) and then reference the Pivot Table values. This way the random values won’t change until the Pivot Table is updated.

   Bagaimana mendapatkan angka random di Excel?

   Jika ingin menggunakan fungsi RAND untuk menghasilkan angka acak tetapi tidak ingin angka berubah setiap kali sel dihitung, Anda dapat memasukkan =RAND() di bilah rumus, lalu tekan F9 untuk mengubah rumus menjadi angka acak.

   Rumus Randbetween untuk apa?

   Mengembalikan angka bilangan bulat acak di antara angka-angka yang Anda tentukan.

   Bagaimana cara menghidupkan Analysis ToolPak pada Excel?

   Memuat dan mengaktifkan Analysis ToolPak.

   Klik tab File, klik Opsi, lalu klik kategori Add-In..

   Di kotak Kelola, klik Add-in Excel, lalu klik Jalankan. Jika Anda menggunakan Excel untuk Mac, dalam menu file, masuk ke Add-in > Excel Tools..

   Di kotak Add-In, centang kotak Analysis ToolPak, lalu klik OK..

   Bagaimana cara memunculkan Data Analysis di Excel?

   Klik menu Alat, lalu klik Add-in Excel. Dalam kotak Add-in tersedia, pilih kotak centang Analysis ToolPak, lalu klik OK. Jika Analysis ToolPak tidak terdaftar dalam kotak Add-in tersedia, klik Telusuri untuk menemukannya.