Extending Chris’ question about random, to 3 ranges of numbe

[eliW]

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 ]

[Chris Davison]

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 ]

[Jay Petrulis]

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

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

[Chris Davison]

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 ]

[Aladin Akyurek]

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

[Chris Davison]

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

[eliW]

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 ]

[Chris Davison]

Nice work Eli, again.

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

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.

[Jay Petrulis]

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