Trying To Understand A Formula...

The =INDEX() formula in your case works like this:

=INDEX("array","row number")

Where "array" is the variable you want to return the contents from; in your case this is $A:$A.

Where "row number" is the row from which you want to return the contents. At this point you have identified a single cell in your worksheet.

The "row number" is just generated using ROUND(RAND()*COUNTA($A:$A),0). This generates a random number between 0 and 1, then multiplies it with the number of cells in $A:$A that are not empty, and then rounds it off to the nearest whole number.

I hope this helps, otherwise try using the evaluate formula tool.

Hi Langadune,

Excel has a built in tool called the Formula Evaluate tool that steps through how it evaluates a formula. See http://excel.tips.net/T002999_Evaluating_Formulas.html for an explaination. I use it frequently when I want to see what Excel thinks is happening with a formula.

Originally Posted by Søren Larsen

The =INDEX() formula in your case works like this:

=INDEX("array","row number")

Where "array" is the variable you want to return the contents from; in your case this is $A:$A.

Where "row number" is the row from which you want to return the contents. At this point you have identified a single cell in your worksheet.

The "row number" is just generated using ROUND(RAND()*COUNTA($A:$A),0). This generates a random number between 0 and 1, then multiplies it with the number of cells in $A:$A that are not empty, and then rounds it off to the nearest whole number.

I hope this helps, otherwise try using the evaluate formula tool.

I would like to add that INDEX used in this matter (INDEX(array,row,0) returns a reference to the range defined by the row_num parameter ( and not to a single cell)

Fi =INDEX(A1:C5,2,0) returns a reference to range A2:C2

Of course if the range is A:A it returns a reference ( or the value) of a single cell because all rows only have one cell.

This will also be the case if the formula is entered in a single cell. It then gives the value of the first cell of the referenced range

For the fun select a row of empty cells and array enter the INDEX formula

I wouldn't advise you to use this formula. The use of ROUND means that the words at the end will appear less often so your selection isn't as "random" as you think (for example if you have 10 words in A1:A10 then the one in A10 will appear less often). This version would be better

=INDEX($A:$A,RAND()*COUNTA($A:$A)+1)

Assumes you have continuous data from A1 down, no gaps

Yes I think you're right. It would appear that it wasn't introduced until Excel 2002.