+ Reply to Thread
Results 1 to 6 of 6

IF statement to evaluate result of random number generator to generate next random number

  1. #1
    Registered User
    Join Date
    05-21-2013
    Location
    Cape Town
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    9

    Question IF statement to evaluate result of random number generator to generate next random number

    I need to scan for random account numbers through a sheet containing account numbers and their details. I have used the following formula to do this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The catch is that column A of the sheet, which contains the account numbers, also contains blank cells between account numbers, and the account numbers are not evenly spaced from each other, and I require the formula to return only account numbers and not blank cells.

    My solution to this has been to try to evaluate the result of the INDEX-RANDBETWEEN formula above using an IF statement in order to decide whether I want to try run the formula again so that I can get a different result.

    I have used:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In the THEN condition of the IF statement I changed the RANDBETWEEN range to start on the third row to try and "force" a new random process from occuring (not sure if this is necessary). I have gone on to nest this process as far as probabilistically sufficient in my mind but the formula seems still to return a disproportionate number of blank results, leading me to believe that nesting these functions like this does not force Excel to try a different random cell on the RETRENCHMENT sheet.

    My end result of nesting was this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is there a more efficient way (and that works) of letting Excel perform a random number generation, having it evaluate the result of this process and then based on this result either return the result or re-run the random number generation to return a different result?
    Last edited by captive; 06-20-2013 at 09:16 AM. Reason: FORMULA tags

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF statement to evaluate result of random number generator to generate next random num

    When you say:

    return a different result?
    Does that mean you don't want the same random number to be generated more than once?

    You could make a list of the row numbers that actually contain data and then get a random row number from this new list. However, getting duplicate random numbers is likely (just about guaranteed!)

    Sound like something you'd want to do?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    05-21-2013
    Location
    Cape Town
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    9

    Re: IF statement to evaluate result of random number generator to generate next random num

    Thanks, and yes I would like a new random number to be generated, although I don't think Excel works like that (not sure), so I'm assuming just letting the range start one row lower does not make a big difference?

    The problem with making a list of row numbers that do contain data is that the sheet from which the account numbers are to be pulled is a live sheet that is updated constantly, so the number of accounts listed and the positions in the sheet of their account numbers will keep changing, so I need something more dynamic to locate them from another sheet.

    Any ideas?

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF statement to evaluate result of random number generator to generate next random num

    There are ways to generate unique random numbers but it's extremely complicated.

    I'm not sure I follow you on the "constantly updating" aspect.

    If the raw data is constantly updating then so will the formulas that refer to the raw data.

    Maybe you need a VBA procedure to do what you want?

  5. #5
    Registered User
    Join Date
    05-21-2013
    Location
    Cape Town
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    9

    Re: IF statement to evaluate result of random number generator to generate next random num

    Thanks. By constantly updating I mean that a colleague enters data in the other sheet on a near daily basis.

    I have found a semi-solution that never returns blank values, only account numbers, although it seems to be not entirely random (I still can't figure out why). Luckily it is not required to be 100% random, but it still would be best if it were.

    I am using the following formula now:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I'd wager that the reason this formula does not return truly random account numbers from the RETRENCHMENT sheet is that my MATCH function is set to find the largest account number that is less than or equal to the randomly generated pseudo account number, and that the account numbers are not sorted in ascending order in the RETRENCHMENT sheet, although the exact logic behind such a conclusion still escapes me.

    NOTE: I have also discovered that I misunderstood the RANDBETWEEN() function - using
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will show quickly that a different random process is used for every instance of the RANDBETWEEN() function.
    Last edited by captive; 06-21-2013 at 06:41 AM. Reason: Added note

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF statement to evaluate result of random number generator to generate next random num

    Quote Originally Posted by captive View Post
    Thanks. By constantly updating I mean that a colleague enters data in the other sheet on a near daily basis.
    I thought that maybe you had something like a dynamic data exchange (DDE) link that updated every second or so. Like a DDE link to a stock exchange. Now that would be updating constantly!

    I have also discovered that I misunderstood the RANDBETWEEN() function - using

    =RANDBETWEEN(0,10)&"-"&RANDBETWEEN(0,10)

    will show quickly that a different random process is used for every instance of the RANDBETWEEN() function.
    Yes, that is correct.

    The only thing I can think of that will do what you want is as I already mentioned, extract the row numbers to a contiguous range of cells and then use a fromula to get one of those row numbers in random order.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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