Forum Discussion
Need Help with Making Random selections from chart.
I am working on an Excel spreadsheet to help me stay on top of some extensive vocabulary and grammar points. I want the first row to generate values from a random cell corresponding to cells in the same column in the chart directly below. I have input a formula from online but some of the cells show "#REF" when generating a random value. It shows in multiple cells at once. I have no idea why the formula does not work every time. I cannot seem to get control of the "#REF". What is a good formula to use to generate a value from a random cell in a specific column? If the one I have is good, how can I fix the "#REF" issue??
Formula is: =INDEX($[COLUMN]$[ROW]:$[COLUM]$[ROW],RANDBETWEEN([TOP ROW],[BOTTOM ROW]))
Example: =INDEX($A$2:$A$15,RANDBETWEEN(2,15))
Microsoft's documentation on the INDEX function is weak. The row number argument is not the spreadsheet row number; it's the relative row number within the specified range/array/table, starting from 1. (See 0:36 in the video.) So your example formula should be:
=INDEX($A$2:$A$15,RANDBETWEEN(1,14))
