How to Generate Random Numbers with Different Percentages in Excel

In this article, we will learn how to use the Excel RANDOM formula with percentages for each random number set. This is a useful technique when we want to generate random numbers that follow a certain distribution or probability. For example, we may want to simulate a dice roll, a lottery draw, or a survey result.

The basic idea is to use the RAND or RANDBETWEEN functions to generate random numbers between 0 and 1, and then use the IF or CHOOSE functions to assign different values based on the percentages we want. For example, if we want to generate a random number between 1 and 10, where 1 has a 90% chance of appearing, and the other numbers have a 10% chance of appearing, we can use this formula:

=IF (RAND ()<0.9,1,RANDBETWEEN (2,10))

This formula works by generating a random number between 0 and 1 using the RAND function, and then checking if it is less than 0.9. If it is, the formula returns 1. If it is not, the formula returns a random number between 2 and 10 using the RANDBETWEEN function.

Procedures

To use the Excel RANDOM formula with percentages for each random number set, we need to follow these steps:

• Define the values and percentages we want to use for each random number set. For example, we may want to generate a random number between 1 and 100, where 1 to 10 have a 50% chance of appearing, 11 to 20 have a 30% chance of appearing, and 21 to 100 have a 20% chance of appearing.
• Calculate the cumulative percentages for each value or range of values. For example, for the above scenario, we can use this table:

• Use the RAND or RANDBETWEEN functions to generate a random number between 0 and 1, and then use the IF or CHOOSE functions to assign the corresponding value or range of values based on the cumulative percentages. For example, for the above scenario, we can use this formula:

=IF (RAND ()<=0.5,RANDBETWEEN (1,10),IF (RAND ()<=0.8,RANDBETWEEN (11,20),RANDBETWEEN (21,100)))

This formula works by generating a random number between 0 and 1 using the RAND function, and then checking if it is less than or equal to 0.5. If it is, the formula returns a random number between 1 and 10 using the RANDBETWEEN function. If it is not, the formula generates another random number between 0 and 1 using the RAND function, and then checks if it is less than or equal to 0.8. If it is, the formula returns a random number between 11 and 20 using the RANDBETWEEN function. If it is not, the formula returns a random number between 21 and 100 using the RANDBETWEEN function.

Example

To illustrate the Excel RANDOM formula with percentages for each random number set, let’s use a scenario where we want to generate a random number between 1 and 5, where 1 has a 40% chance of appearing, 2 has a 20% chance of appearing, 3 has a 15% chance of appearing, 4 has a 15% chance of appearing, and 5 has a 10% chance of appearing. We can use this table to calculate the cumulative percentages:

We can use this formula to generate the random number:

=IF (RAND ()<=0.4,1,IF (RAND ()<=0.6,2,IF (RAND ()<=0.75,3,IF (RAND ()<=0.9,4,5))))

We can use an Excel table to show the results of applying this formula to 10 cells:

Other approaches

There are other ways to use the Excel RANDOM formula with percentages for each random number set. For example, we can use the CHOOSE function instead of the IF function to select a value based on a random index. The CHOOSE function takes a number as the first argument, and returns the value at that position from the following arguments. For example, we can use this formula to generate a random number between 1 and 5, where 1 has a 40% chance of appearing, 2 has a 20% chance of appearing, 3 has a 15% chance of appearing, 4 has a 15% chance of appearing, and 5 has a 10% chance of appearing:

=CHOOSE (RANDBETWEEN (1,10),1,1,1,1,2,2,3,4,5)

This formula works by generating a random number between 1 and 10 using the RANDBETWEEN function, and then using the CHOOSE function to return the value at that position. The values are arranged in such a way that 1 appears four times, 2 appears twice, and 3, 4, and 5 appear once, which corresponds to the percentages we want.

Another approach is to use the VLOOKUP function to look up a value based on a random number and a table of cumulative percentages. For example, we can use this formula to generate a random number between 1 and 5, where 1 has a 40% chance of appearing, 2 has a 20% chance of appearing, 3 has a 15% chance of appearing, 4 has a 15% chance of appearing, and 5 has a 10% chance of appearing:

=VLOOKUP (RAND (),$F$2:$G$6,2,TRUE)

This formula works by generating a random number between 0 and 1 using the RAND function, and then using the VLOOKUP function to look up the value in column G based on the table in F2:G6.