Once the value in cell E2 is 45% or greater, the new returned value in E4 would become Value 3 and so forth
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 We can use the MATCH function to find the value that is within the probability range. The match function returns the ROW in which the closest data match is found. See below Lookup Value Using MATCH Function 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. Random value generation using MATCH and RAND functions This is the specified probability of Value 4 being selectedįigure 2. Take the difference between 85% and 55% which equals 30%.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 This is because 0 starts the range between 0 and 1, so the first value in Column C must equal zero. Notice how we purposely offset the formula by 1 row.In cell C3, we type this formula =sum(B1,C1) and copy the formula down to the bottom of our data 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.Random value generation using MATCH and RAND functions Creating a Cumulative Probability 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.įigure 1. Using Excel to Calculate Random Number Weighted ProbabilityĮxcel can return a value randomly based on its probability by using SUM, MATCH and RAND functions.
0 kommentar(er)
