# Trying To Understand A Formula...

1. ## Trying To Understand A Formula...

I'm still pretty new at this and am trying to learn what all this means... That being said, I've been using a formula to select a random word from a list and display it in another cell.

=INDEX(\$A:\$A,ROUND(RAND()*COUNTA(\$A:\$A),0))

What I'd like to know is if someone could break this down I could understand WHY it works. For instance, ROUND and RAND seem like number functions to me and not text. I think the COUNTA part is only telling the formula to select from cells that have entries in them.

Thanks!  Register To Reply

2. ## Re: 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.  Register To Reply

3. ## Re: Trying To Understand A Formula...

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.  Register To Reply

4. ## Re: Trying To Understand 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  Register To Reply

5. ## Re: Trying To Understand A 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  Register To Reply

6. ## Re: Trying To Understand A Formula... Originally Posted by daddylonglegs 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

Thanks that's one of the things I was wondering about. Someone else gave me the formula and I wasn't sure exactly what it was doing. My index is actually quite long, so this might make a difference.

MarvinP and Larsen... I have Excel 2000 and it doesn't seem to have the Formula Evaluate function you referenced.  Register To Reply

7. ## Re: Trying To Understand A Formula...

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

8. ## Re: Trying To Understand A Formula... Originally Posted by daddylonglegs 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
This formula really simplifies things and I think I have a good handle on it now... however, what does changing my ,0 to +1 accomplish?  Register To Reply