Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Extending Chrisí question about random, to 3 ranges of numbe

  1. #1
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 30/3 Chris Davison posted a question in:
    http://www.mrexcel.com/board/viewtop...orum=2&start=0

    Evening all,
    how can I return a single random number from the set : 1 to 10 and 20 to 30

    ie

    8 would be okay
    2 would be okay
    23 would be okay
    29 would be okay

    but 15 would not be okay
    and 18 would not be okay

    Just one random number from those ranges 1-10, 20-30
    It was answered by using a single formula.

    Now how can it be done for 3 ranges of numbers, say: 1-10, 21-30, 51-60, in one single formula?

    Eli


    [ This Message was edited by: eliW on 2002-04-01 07:49 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Eli,

    I forgot to thank you for that, my apologies

    I liked the way you'd split the randomness into the size of the population and then "if"d it accordingly on the proportion of the split in the whole population 40:60 (or whatever it was)... very clever

    maybe with 3 ranges of sizes 10:15:8 (or whatever) you can apply the same logic and incorporate an appropriate number of nested IFs ?



    edit : I'd be tempted to not make the ranges equal in size though, it forces us to think of proper random samples (my original question was just a fluke of equal sizes, I hadn't meant it to be)

    [ This Message was edited by: Chris Davison on 2002-04-01 07:57 ]

    [ This Message was edited by: Chris Davison on 2002-04-01 08:00 ]

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-01 07:45, eliW wrote:
    On 30/3 Chris Davison posted a question in:
    http://www.mrexcel.com/board/viewtop...orum=2&start=0

    Evening all,
    how can I return a single random number from the set : 1 to 10 and 20 to 30

    ie

    8 would be okay
    2 would be okay
    23 would be okay
    29 would be okay

    but 15 would not be okay
    and 18 would not be okay

    Just one random number from those ranges 1-10, 20-30
    It was answered by using a single formula.

    Now how can it be done for 3 ranges of numbers, say: 1-10, 21-30, 51-60, in one single formula?

    Eli


    [ This Message was edited by: eliW on 2002-04-01 07:49 ]
    Hi Eli,

    For up to 30 (?) different ranges, use the CHOOSE function, with the index as a randbetween (like your very nice example with two ranges).

    =CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(1,10),RANDBETWEEN(21,30),RANDBETWEEN(51,60))

    Gotta run now, but I will work on the unequal sized ranges when I get time.

    Bye,
    Jay

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    consider the following (unequal) populations :

    1) 2 to 8
    2) 15 to 23
    3) 44 to 100

    7, 9 and 57 numbers respectively, total population of 73 numbers

    so take a random number between 0 and 72

    if it's =<6 then take a random number between 2 and 8

    if it's between 7 and 15 then take a random number between 15 and 23

    if it's =>16 then take a random number between 44 and 100

    would this work, statistically ?

    [ This Message was edited by: Chris Davison on 2002-04-01 08:20 ]

    [ This Message was edited by: Chris Davison on 2002-04-01 08:21 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-01 07:45, eliW wrote:
    On 30/3 Chris Davison posted a question in:
    http://www.mrexcel.com/board/viewtop...orum=2&start=0


    Evening all,
    how can I return a single random number from the set : 1 to 10 and 20 to 30

    ie

    8 would be okay
    2 would be okay
    23 would be okay
    29 would be okay

    but 15 would not be okay
    and 18 would not be okay

    Just one random number from those ranges 1-10, 20-30
    [/font]

    It was answered by using a single formula.

    Now how can it be done for 3 ranges of numbers, say: 1-10, 21-30, 51-60, in one single formula?


    Eli,

    How about:

    =EVAL(VLOOKUP(RANDBETWEEN(1,3),{1,"RANDBETWEEN(1,10)";2,"RANDBETWEEN(21,30)";3,"RANDBETWEEN(51,60)"},2,0))

    where I use EVAL, a UDF by Longre?

    Aladin

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    that's nice Aladin..... my IF logic falls down at the first hurdle where the first ELSE arguement comes into play..... it can't be referenced back to the original =RAND function with more than 2 possibilities

  7. #7
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you guys for your sophisticated answers:

    Aladin suggested using VLOOKUP function combined with UDF:

    =EVAL(VLOOKUP(RANDBETWEEN(1,3),{1,"RANDBETWEEN(1,10)";2,"RANDBETWEEN(21,30)";3,"RANDBETWEEN(51,60)"},2,0))

    where I use EVAL, a UDF by Longre?
    Aladin
    That would solve the problem of multiconditional random as Chris wrote later:

    "that's nice Aladin..... my IF logic falls down at the first hurdle where the first ELSE arguement comes into play..... it can't be referenced back to the original =RAND function with more than 2 possibilities"
    It is really nice but can be done in simpler way by IF function only:

    =IF(RANDBETWEEN(0,2)=0,RANDBETWEEN(1,10),IF(RANDBETWEEN(0,1)=0,RANDBETWEEN(21,30),RANDBETWEEN(51,60)))

    This approach can be used with some changes also to different size ranges.

    Thank you all,
    Eli



    [ This Message was edited by: eliW on 2002-04-01 14:47 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nice work Eli, again.

    I missed the ELSE logic.... you don't need to refer back, you just recalc again on 0 or 1....hot****

    I suppose the extent to which this can be extrapolated (using known population sizes) is only limited by the maximum number of nested IFs allowable.


  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-01 08:18, Chris Davison wrote:
    consider the following (unequal) populations :

    1) 2 to 8
    2) 15 to 23
    3) 44 to 100

    7, 9 and 57 numbers respectively, total population of 73 numbers

    so take a random number between 0 and 72

    if it's =<6 then take a random number between 2 and 8

    if it's between 7 and 15 then take a random number between 15 and 23

    if it's =>16 then take a random number between 44 and 100

    would this work, statistically ?

    [ This Message was edited by: Chris Davison on 2002-04-01 08:20 ]

    [ This Message was edited by: Chris Davison on 2002-04-01 08:21 ]
    Hi Chris,

    =IF(RANDBETWEEN(1,73)>16,RANDBETWEEN(44,100),IF(RANDBETWEEN(1,16)>7,RANDBETWEEN(15,23),RANDBETWEEN(2,8)))

    or

    =IF(RANDBETWEEN(1,73)<=57,RANDBETWEEN(44,100),IF(RANDBETWEEN(1,16)<=9,RANDBETWEEN(15,23),RANDBETWEEN(2,8)))

    will give you a random number with your criteria, but I am not too sure that this will hold statistically.

    Each IF has a separate RANDBETWEEN as the test, so I am questioning whether the 2nd and 3rd arguments are "handicapped" by having to go through multiple selections.

    It appears that the change from RANDBETWEEN(1,73) to RANDBETWEEN(1,16) handles this issue, but I cannot be sure that the results are in the correct proportions (7/73; 9/73; 57/73).

    Regards,
    Jay

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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