Excel Formulas to Generate Random Number Weighted Probability!!

Excel can return a value randomly based on its probability by using SUM, MATCH, and RAND functions. In this article, I introduce some formulas which can quickly generate the random number weighted with a given probability in Excel. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula:

If you want to generate the random number with weight probability, you can use the below formula.

*=MATCH(RAND(),cumulative_probability) *

Syntax Explanations:

MATCH – This function will help to extract the texts with the given number of characters from the right side. Read more on the RIGHT Function.
RAND – In Excel, the LEN function returns the length of a given text string as the number of characters.
Cumulative_probability – It specifies the weighted probability.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Let’s consider the below example image.

First, we will enter the input names in Column B, Column C , and Column D.
Now we need to generate a random number, weighted with a given probability.
Here the Cell D3 contains the following formula:

=SUM(D2,C2)

Select any cell and type the above-given formula.

Finally, press the ** ENTER** key, you can get the result as shown below.

Bottom-Line:

Here we have described the simple formulas to create the random number weighted probability in Excel. Let me know if you have any doubts regarding this article or any other Excel/VBA topic.

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **