Today we will learn how we can apply weighted probability in order to generate random numbers in Excel. Sometimes we may have to generate or choose a number randomly from a set of specified numbers, but their probability of getting chosen or generated might not be uniform but weighted. For example, the probability of a footballer scoring 5 goals in a game is less probable than scoring 1,2 or 3 goals. In this situation, generating random numbers without considering the weightage factor will not give the desired result.

**How to Apply Weighted Probability in Excel: 3 Useful Methods**

In this section, we will demonstrate 3 effective methods to generate a random number weighted with a given probability in Excel with appropriate illustrations. Here, we have some numbers in one column, and on the next right column, we have assigned a probability of them.

Now, we aim to generate/choose one number randomly from 1 to 6 but in such a way that the probability of getting a number remains its assigned probability. For example, if we generate 1000 random numbers in an Excel sheet between 1 to 6,Â we should get 1,2, and 3 each around 100 times, 4 around 200 times, and 5 and 6 around 250 times. To do that, we can follow 3 different methods. Letâ€™s begin with the first method.

### 1. Combining MATCH & RAND Functions to Apply Weighted Probability in Excel

In this method, we will use the **MATCH** and **RAND** functions to generate random numbers based on weighted probability in Excel. To do that, we need to create an extra column in the data set containing the cumulative probability. Follow the steps below.

**Steps:**

- At first, give a column heading such as
**Cumulative**in cell

- Now, on cell
**D5**, the cumulative will be**0**. But on cell**D6**, it will be the sum of**C5**. So put the following formula in cell**D6**.

`=SUM(C5,D5)`

- After that, use theÂ
**Fill Handle**to copy the formula into the rest of the cells on the cumulative column.

- Next, to generate a random number, take another column and name it
**Results**.

- Then, on cell
**F5**, write down the following formula and press**Enter**. You should get a number between**1**to**6**.

`=MATCH(RAND(),$D$5:$D$10)`

- Now, we will again use
**Fill Handle**to copy the formula into the other cells below.

- Since we have used the
**RAND**function, the values obtained now will be changed if any further operation is performed. To prevent changing values, we can copy and paste the values. As a result, we will lose the formula on the cells, but they will not get changed in the future. - Thus, our final result should be like this.

- As we have taken only a small number of cells, the frequency of getting a number is not entirely obeying the assigned probability. But if we generate a large set of numbers, the frequency will be very close to the assigned probability.

**🔎 ****How Does the Formula Work?**

**RAND()**

Here, the **RAND **function generates a random number between 0 and 1.

**MATCH(RAND(),$D$5:$D$10)**

Here, the **MATCH **function takes the first argument from the **RAND **function and searches the value in the range of** D5:D10**(**Cumulative** column). It returns the largest row number where the value on the Cumulative column<=searched value.

**Note:**

- The numbers must be arranged in ascending order in the
**Value** - Here, the return value of the
**MATCH**function is the same as the numbers to be randomly generated. If this is not the case, we can not use this method. As a result, we have to use the 2ndÂ method.

**Read More:Â **How to Get Simulation Probability in Excel

### 2. Apply Weighted Probability in Excel Utilizing INDEX, MATCH & RAND Functions

In this method, it is not necessary to generate the same values as the row number of the weightage column. You can have values like the figure below.

Now, in this case, we will use **the INDEX function**. Follow the steps below.

**Steps:**

- First, in cell
**F5**, write down the following formula and click**EnterÂ**key.

`=INDEX($B$5:$B$10,MATCH(RAND(),$D$5:$D$10))`

- Then, we can autofill the rest of the cells like the 1st method and get our final result like the figure below.

**🔎 ****How Does the Formula Work?**

**MATCH(RAND(),$D$5:$D$10))**

Like the 1st method,** MATCH(RAND(),$D$5:$D$10))** will yield integer values ranging from 1 to 6. This value will be passed as 2nd argument of **the INDEX function.**

**INDEX($B$5:$B$10,MATCH(RAND(),$D$5:$D$10))**

The formula as a whole will return the cell value from the **Value** column(**B5:B10**) corresponding to the row number that was passed by the **MATCH **function.

**Note:**

- The values we want to generate randomly are not mandatory to be numerical only. They can be text as well.

**Read More:Â **How to Create Option Probability Calculator in Excel

### 3. Using INDEX, COUNTIF & RAND Functions

In this method, we can use **the COUNTIF function** instead of the **MATCH **function to randomly generate not only numerical values but also text values. For this method, we have taken another example where we have five persons, each with a certain probability of appearing.

Here, an important thing to notice is that, unlike the previous two methods, in the cumulative column, we started with **0.10** and then added them in the subsequent cells of the columns, and it ended with a **1.0** value. Now follow the steps below.

**Steps:**

- Firstly, in cell
**F5**, write the following formula

`=INDEX($B$5:$B$10,COUNTIF($D$5:$D$10,"<="&RAND())+1)`

- Then, if we autofill the formula for the rest of the cells, we will get the following results.

- Consequently, from the above figure, it is evident that the person with a larger probability weightage has appeared more often than the lesser ones.

**🔎**** How Does the Formula Work?**

**RAND()**

This function generates a random number between 0 and 1

**RAND())+1**

This adds 1 with the randomly generated value.

**COUNTIF($D$5:$D$10,”<=”&RAND())+1)**

It counts the number of cells in the **Cumulative **column(**D5:D10**) that are less than **RAND())+1**

**INDEX($B$5:$B$10,COUNTIF($D$5:$D$10,”<=”&RAND())+1)**

It returns the cell value of column **Person**(**B5:B10) **corresponding to the row number that is taken as the 2nd argument of the **INDEX **function.

**Read More:Â **How to Calculate Empirical Probability with Excel Formula

**Things to Remember**

- The
**1st**method is only applicable if the row numbers and the set of generated numbers are the same. **2nd**and**3rd**are applicable for both generating numerical and numerical values.- The
**3rd**method has a different way of calculating the Cumulative column.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

That is the end of this article. Hopefully, you have understood the methods described here to apply weighted probability in Excel. If you find this article helpful, please share this with your friends. Furthermore, do let us know if you have any further queries.

## Related Articles

- How to Calculate Probability Density Function in Excel
- Probability Formula for Lottery in Excel
- How to Make a Probability Tree Diagram in Excel

**<< Go Back to Excel Probability | Excel for Statistics |Â Learn Excel**