Excel allows us to create a weighted random number generator using the SUM, MATCH and RAND functions. This step by step tutorial will assist all levels of Excel users to get the random weighted numbers from the list and to place them in the separate column.
Figure 1. The final result of the formula
Syntax of the SUM Formula
=SUM(number1, [number2], ...)
The parameters of the SUM function are:
- number1, [number2] – numbers to sum
Syntax of the MATCH Formula
=MATCH(lookup_value, lookup_array, [match_type])
The parameters of the MATCH function are:
- lookup_value – a value which we want to find in the lookup_array
- lookup_array – the array where we want to find a value
- [match_type] – a type of match. We put 0 which is an exact match.
Syntax of the RAND Formula
The generic formula for the RAND function is:
=RAND()
The function returns a random decimal number between 0 and 1 and has no parameters.
Setting up Our Data to Get a Weighted Random Number
Our table consists of 3 columns: “Value” (column B), “Probability” (column C) and “Cumulative” (column D). In column “Cumulative” we will place the cumulative probability starting from the value 0. The second table has column “Weighted Random Number” (column F) where we will extract the weighted random numbers from column B.
Figure 2. Data for the example
Get Weighted Random Number with SUM, MATCH and RAND Functions
We want to get the random weighted values from column B and to place the results in the column F. In order to make the formula more clear, we will create a named range Cumulative for cell range D3:D8.
To create a named range we should follow the steps:
- Select the cell range that should be named
- Click on the name box in Excel
- Write the name for the cell range and press enter
Figure 3. Creating a named range Cumulative for column “Cumulative”
The formula in Column D looks like:
=SUM(D2,C2)
The SUM function calculates the cumulative probability starting from number 0. This is important for MATCH formula part and will be explained later. Also, values in column D should be sorted out in the ascending order.
To apply the formula, we need to follow these steps:
- Select cell D3 and click on it
- Insert the formula:
=SUM(D2,C2) - Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.
Figure 4. Using the SUM formula to get a cumulative probability starting from 0
Now we can place the formula in the cell F3 to get the random weighted number:
=MATCH(RAND(),Cumulative)
The parameter lookup_value in the MATCH function is a formula RAND() while the lookup_array is the named range Cumulative. The match_type is omitted because we want the approximate match.
To apply the formula, we need to follow these steps:
- Select cell F3 and click on it
- Insert the formula:
=MATCH(RAND(),Cumulative) - Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.
Figure 5. Extract random weighted number with SUM, MATCH and RAND functions
The RAND function returns the random number between the 0 and 1. The MATCH function searches RAND function result in the column “Cumulative”. As we can see, argument match_type is omitted and MATCH function is searching for an approximate match in the column D. That’s why the cumulative probability is starting from the value 0.
In order to cover all numbers between 0 and 1 (RAND function results) value in the cell D3 must start from 0. The formula output is number 6 because the RAND function result is somewhere between the value 0.70 and 1.
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.
Leave a Comment