To generated a random number, weighted with a given probability, you can use a helper table together with a formula based on the RAND and MATCH functions. Formula =MATCH(RAND(),cumulative_probability) Explanation In the example shown, the formula in F5 is: =MATCH(RAND(),D$5:D$10) How this formula works This formula relies on the helper table visible in the range …
To generate random dates between two dates, you can use the RANDBETWEEN function, together with the DATE function. See example below: Formula =RANDBETWEEN(date1,date2) Explanation In the example shown, the formula in B5 is: =RANDBETWEEN(DATE(2016,1,1),DATE(2016,12,31)) This formula is then copied down from B5 to B11. The result is random dates between Jan 1, 2016 and Dec …
To randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions. Formula =ROUNDUP(RANK(A1,randoms)/size,0) Explanation In the example shown, the formula in D5 is: =ROUNDUP(RANK(C5,randoms)/size,0) which returns a group number …
To randomly people (or anything) to groups you can use the RANDBETWEEN function with the CHOOSE function. Formula =CHOOSE(RANDBETWEEN(1,3),”Group A”,”Group B”,”Group B”) Explanation In the example shown, the formula in F3 is: =CHOOSE(RANDBETWEEN(1,3),”A”,”B”,”C”) When copied down the column, this formula will generate a random group (A, B, or C) for each person in the list. …
To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions. Formula =INDEX(data,RANDBETWEEN(1,ROWS(data)),1) Note: this formula uses the named range “data” (B5:E104) for readability and convenience. If you don’t want to use a named range, substitute $B$5:$E$104 instead. Explanation In …
To generate random times in at specific intervals you can use the RAND function with the FLOOR function. See example below: Formula =FLOOR(RAND(),”0:15″) Explanation In the example shown, the formula in B6 is: =FLOOR(RAND(),”0:15″) which generates a random time at a 15-minute interval. How this formula works The RAND function generates a decimal number between …
To quickly fill a range of cells with random text values, you can use a formula based on the CHOOSE and RANDBETWEEN functions. Formula =CHOOSE(RANDBETWEEN(1,3),”Value1″,”Value2″,”Value3″) Note that RANDBETWEEN will calculate a new value whenever the worksheet is changed. Once you have values in the range, you may want to replace the formulas with values to …
To quickly fill a range of cells with random numbers from a set of fixed options (i.e. 25,50,75,100), you can use a formula based on the CHOOSE and RANDBETWEEN functions. Formula =CHOOSE(RANDBETWEEN(1,4),num1,num2,num3,num4) Explanation In the example shown, the formula in B4 is: =CHOOSE(RANDBETWEEN(1,4),25,50,75,100) Which returns a random number from the numbers provided. How this formula …
To generate random integers between two numbers, you can use the RANDBETWEEN function. Formula =RANDBETWEEN(lower,upper) Explanation In the example shown, the formula in B5 is: =RANDBETWEEN(1,100) This formula is then copied down from B5 to B11. The result is random numbers between 1-100. Note that the RANDBETWEEN function will generate new numbers whenever a change …