During corporate events and holidays, giveaways are given to employees or family members. Use Excel’s RANDBETWEEN() and INDEX() Functions to select random contestant winners fairly.
This article provides the functions’ description and syntax. Watch the video to see the power of these features.
RANDBETWEEN() FUNCTION
The RANDBETWEEN() returns the random row number within a range. A new row number is given after each recalculation.
Syntax: RANDBETWEEN(bottom,top)
The bottom argument refers to the lowest row number in the range.
The top argument refers to the highest row number in the range.
Example: =RANDBETWEEN(2,1663)
This example will display a random row number between Rows 2 and 1663.
There are two ways to reset the value in the cell.
- Edit the by pressing F2, then Enter
- Use the manual recalculate command by pressing F9
Now, give it a try.
INDEX() FUNCTION
The INDEX() Function returns the specified row or column value within a range.
Syntax: Index(array, row_number, column_number)
The array refers to the cells range containing the data. For example, A2:A1663 highlights row2 2 through 1663 in column A.
The Row_number is the row containing the value you want to display.
The Column_number is the column containing the value you want to display.
Note: The formula must contain either a row or column number reference, but you do not have to reference both. See examples below.
Example 1: =Index(a2:a1663,24)
Since the one column of data is highlighted, the results show the value in row 24 within the range of rows 2 and 1663.
Example 2: =Index(a2:b1663,24,2)
This example highlights two columns from rows 2 through 1663. The results show the value in row 24 of column 2.
USING RANDBETWEEN WITH INDEX FUNCTION
Combining these functions, you will observe the effectiveness of returning the actual value in a specified row or column.
SYNTAX: =Index(array,randbetween(bottom,top))
Example: =Index(a2:a1663,randbetween(2,1663))
Since the RANDBETWEEN displays a row number, the above formula only needs the index range specified.
The 4E Takeaway
These functions are rarely used together to determine a winner. And, it is a shame.
The next time you are working with digital lists from a community event. Don’t sort the names in alphabetic order, and ask a co-worker to pick a number. Just “Excel-it.”