+ Reply to Thread
Results 1 to 8 of 8

Trying To Understand A Formula...

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2000
    Posts
    5

    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!

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    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.
    Sincerely
    Søren Larsen

    "Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,513

    Re: Trying To Understand A Formula...

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,178

    Re: Trying To Understand A Formula...

    Quote Originally Posted by Søren Larsen View Post
    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

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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
    Audere est facere

  6. #6
    Registered User
    Join Date
    05-22-2012
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2000
    Posts
    5

    Re: Trying To Understand A Formula...

    Quote Originally Posted by daddylonglegs View Post
    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.

  7. #7
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Trying To Understand A Formula...

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

  8. #8
    Registered User
    Join Date
    05-22-2012
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2000
    Posts
    5

    Re: Trying To Understand A Formula...

    Quote Originally Posted by daddylonglegs View Post
    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1