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
Get FREE Advanced Excel Exercises with Solutions!
