Explanation
The CHOOSE function provides the framework for this formula. Choose takes a single numeric value as its first argument (index_number), and uses this number to select and return one of the values provides as subsequent arguments, based on their numeric index.
In this case, we are using four values: Red, Blue, Green, and Pink, so we need to give CHOOSE a number between 1 and 4.
To generate this number, we use RANDBETWEEN, a function that returns a random integer between a bottom and top value. Since we are only working with 4 values in CHOOSE, we supply 1 for the bottom number and 4 for the top number.
When this formula is copied down, it will return one of the four colors.
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 prevent further changes.
Explanation
The RAND function generates a decimal number between zero and 1. So, you might get output like this from RAND() in three cells:
0.54739314 0.919767722 0.633760119
Dates in Excel are defined as simple numbers, where 1 = 1 day. This means you can simply divide 1 by the decimal value of time to get a value that corresponds to time as Excel sees it, for example:
1/12 = 12 hours = .5 days 1/6 = 6 hours = .25 days 1/8 = 8 hours = .333 days
This means we can use RAND() to generate a decimal value between 1 and 0, then round that number down with FLOOR to the nearest interval. FLOOR takes an argument called significance as the rounding multiple, and it recognizes time intervals expressed like this:
“0:10” - 10 minutes “0:15” - 15 minutes “0:30” - 30 minutes “0:45” - 45 minutes
Setting and upper and lower time
If you want to limit the hours used by RAND, you can use this general formula force RAND to output a number between an upper and lower value:
=RAND()*(upper-lower)+lower
Because Excel can recognize time values, you can get times between 8 AM and 12 PM, with a formula like this:
=RAND()*("12:00"-"8:00")+"8:00"
Note: the formula above is general and will work with other numbers too, not just times.
