This is a very simple table with six entries (A2-A7). A1 is a formula which randomly selects an entry from the table using the INDEX and RANDBETWEEN functions. A8 is a text copy of the function used. Each time you hit the F9 key, RANDBETWEEN recalculates its value, and uses it as an index into the table. Here's what the INDEX function means:
- A2:A7 is the array to work on (that's six rows by one column).
- RANDBETWEEN(1,6) chooses a row value between 1 and 6, inclusive.
- The final 1 parameter chooses the first column.
Points to note:
- The INDEX function takes an array, a row, and a column as parameters. You can get fancier and use RANDBETWEEN for both row and column to create a two-dimensional table.
- The RANDBETWEEN function picks a number from the integer values between and including the given low and high.
- The row and column values in INDEX are values within the given array (A2:A7). The first row/column is always one, and the highest value is equal to the number of rows/columns in the array. Don't use spreadsheet row/column values!
- Using an array reference inline like that leads to unreadable spreadsheets. If we were doing a more complex example, we'd apply a name to the data range, and use that instead.
- What works in Excel generally works in Open Office or Google Drive documents. For example you can see the Drive version right here.
OK, that's it, quick and dirty. There's more complex stuff in this post right here though.