Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# Using Excel to Calculate Random Number Weighted Probability

Excel can return a value randomly based on its probability by using SUM, MATCH and RAND functions. This step by step tutorial will assist all levels of Excel users in randomly creating a list of values based on their probability of being selected.

The Final Formula:

=match(rand(),\$C\$2:\$C\$6)

Figure 1. Random value generation using MATCH and RAND functions

## Creating a Cumulative Probability

• First, we setup our data so that we have the value to be returned in Column A, the probability in Column B and a blank Column C
• In cell C2, we type “0”. In cell C3, we type this formula =sum(B1,C1) and copy the formula down to the bottom of our data
• Notice how we purposely offset the formula by 1 row. This is because 0 starts the range between 0 and 1, so the first value in Column C must equal zero. Then, the rest of the cells in column C will add the previous percentages together to form percentage increments which represents the probability that each value will be selected
• Take the difference between 85% and 55% which equals 30%. This is the specified probability of Value 4 being selected

Figure 2. Random value generation using MATCH and RAND functions

Now that we have the cumulative probability created and we are familiar with the MATCH function, we can now use the RAND function to generate a list of random numbers between 0 and 1 and find the closest lower match of the random number. See below

## Lookup Value Using MATCH Function

The match function returns the ROW in which the closest data match is found. We can use the MATCH function to find the value that is within the probability range. In order to complete this task using other data, we will need to use the INDEX function which will be explained later in the article

MATCH Function Syntax:

=MATCH(Lookup Value, Lookup Table Array, Approximate Match [True/False])

• Enter the lookup probability in cell E2
• In cell E4 type this formula
=match(E2,C2:C6)
• In this example the formula returned Value 2 because 16% is between 15% and 45%. Once the value in cell E2 is 45% or greater, the new returned value in E4 would become Value 3 and so forth

Note: This will only work if the data is numbered in sequence with the number of values available. Therefore, if this was five rows of data, the values would have to be 1, 2, 3, 4 and 5 in ascending order. For other types of data, we will need to use the INDEX function explained later in the article.

*The previous series of steps were used to illustrate how the match function looks up data. In the next series of steps we will generate multiple random values in column E by adding RAND to our MATCH formula to generate a more dynamic matching function.

## Lookup Value Using RAND Function

The RAND function returns a random value between 0 and 1. We will use the RAND function to develop the random lookup.

• In cell E2 type this formula
=MATCH(RAND(),\$C\$2:\$C\$6)
• Be sure to create absolute references (by using the ‘\$’ sign) on your data array and copy the formula down to cell E10
• The formula returns the matching ROW value from column A based on the ROW where Column C matches the random number

### Bonus Step:

If your value list is Text instead of numbers or is not numbered in sequence with the data, you can add the INDEX function to the formula to randomly lookup the text value based on its probability

## Lookup Text Value Using Index Function

• The INDEX function returns a value based on the ROW and COLUMN the user specifies  – Example: “=Index(1,1)” returns the value in cell A1 (ROW 1, COLUMN 1)
• We can use this concept to find the matching row by nesting the INDEX, MATCH and RAND functions
• In cell E2 type this formula
=INDEX(\$A\$2:\$A\$6,MATCH(RAND(),\$C\$2:\$C\$6))
• The formula returns the text value in column A based on it’s probability of being selected
• Be sure to create absolute references on your data arrays and copy the formula down to cell E10

Instant Connection to an Expert through our Excelchat Service:

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

### Did this post not answer your question? Get a solution from connecting with the expert.

Related blogs
Solution examples
I need a formula that will check if 2 or more cells containing names match then add the adjacent cells to matching name together.
Solved by F. D. in 59 mins
How can I automatically change different cells and the information they display if we select different option from a drop down list?
Solved by V. U. in 59 mins
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
Hello, I need help regarding indirect formula of excel. 1. What is the purpose of using this formula? 2. If we use this formula with sum formula, then why we are using this with sum formula, we can also perform addition from different sheets without indirect formula ?
Solved by O. D. in 21 mins
I need a formula to calculate the MAX of a range of 167 rows in column D, then find the MAX of the next 167 rows in column D and so on continuously through the entire sheet. Example MAX D2:D169, D170:D337, D338:D505 etc...
Solved by I. Q. in 60 mins