RAND() Function Within Nested IF Statement

[GWBlack]

Hi All,

I hope everybody is doing well today. My name is Gary and I am new here.

Although I've used the =RAND() function inside nested IF statements before, a simple question just occurred to me. Suppose I want to sample a distribution that returns "1" with 20% probability, "2" with 50% probability, and "3" with 30% probability. I would write this as =IF(RAND()<0.2,1,IF(RAND()<0.7,2,3)

This seems to work in Excel, and it cycles through the various possible values (1, 2 or 3) as I press the F9 function key.

However, wouldn't Excel treat the two RAND() functions as different expressions and, thus, use two DIFFERENT random numbers inside my nested =IF( ) statement? If true, I would probably need to generate a single random number in a single cell and then insert that cell reference in both places inside my =IF( ) statement instead of the RAND( ) function.

Thank you!
Gary

 

[GWBlack]

ON EDIT: I should have added an extra closing (right) parenthesis in my IF statement.

 

[Rick Rothstein]

You could get that randomized distribution percentages like this...

=CHOOSE(RANDBETWEEN(1,10),1,1,2,2,2,2,2,3,3,3)

 

[GWBlack]

You could get that randomized distribution percentages like this...

=CHOOSE(RANDBETWEEN(1,10),1,1,2,2,2,2,2,3,3,3)

Thank you! I've not used the CHOOSE function before.

Will the =IF( ) still work for my purposes, or will it actually be using two different random numbers within that expression?

 

[mikerickson]

How about
=MATCH(RAND(), {0, .2, .7})

or more generically

=LOOKUP(RAND(), {0, 0.2, 0.7}, {"one", "two", "three"})

 

[joeu2004]

Suppose I want to sample a distribution that returns "1" with 20% probability, "2" with 50% probability, and "3" with 30% probability.
I would write this as =IF(RAND()<0.2,1,IF(RAND()<0.7,2,3)

This seems to work in Excel, and it cycles through the various possible values (1, 2 or 3) as I press the F9 function key.

However, wouldn't Excel treat the two RAND() functions as different expressions and, thus, use two DIFFERENT random numbers inside my nested =IF( ) statement? If true, I would probably need to generate a single random number

Correct. Your formula with two calls does not really work, insofar as it does not give you the distribution that you require.

The probability of returning 1 is 20%, but the probability of returning 2 is 56% (0.8 * 0.7), and the probability of returning 3 is 24% (0.8 * 0.3).

That can be confirmed by entering your formula into A1:A10000, then looking at the FREQUENCY distribution of 1, 2 and 3. In one random sampling, my results are 1993, 5602 and 2405.

Instead, use the following formula:

=LOOKUP(RAND(), {0, 0.2, 0.7}, {1, 2, 3})

Again, confirm by entering that formula into A1:A10000, and look at the FREQUENCY distribution of 1, 2 and 3. In one random sampling, my results are 1949, 5071 and 2980.

PS.... Mikerickson's MATCH formula is functionally equivalent and more succinct.

PPS.... Well, arguably not. It is functionally equivalent because your IF formula returned numeric 1, 2 and 3. But suppose you actually did want to return the strings "1", "2" and "3", as you wrote (incorrectly) in your description. The LOOKUP can do that easily. Mikerickson's MATCH formula would need more work. So on second thought, I would opt for the LOOKUP formula.

 

[Tetra201]

Or

=LOOKUP(RANDBETWEEN(1,10),{1,3,8},{1,2,3})

 

[GWBlack]

Thank you again. It seems there are several different functions that will do the trick.

I am writing a section on nested =IF( ) statements, and so I would like to stick with a nested =IF( ) if possible,. Will it work if I generate a single random number using RAND( ) outside of my =IF( ) statement and then call it within my =IF( ) function? In this case, it will only use a single random number. In other words, it would be set up like below where Cell A2 contains the random number being called:

=IF(A2<0.2,1,IF(A2<0.7,2,3))

My reason for using the "<0.7" in the middle (instead of "AND(>=0.2,<0.7)") is because I assumed Excel sequentially evaluates the expression from left to right, and so it would not enter the "<0.7" part of the function unless the random number was >=0.2. Is this not correct?

Thank you again!
Gary

 

[mikerickson]

...
Will it work if I generate a single random number using RAND( ) outside of my =IF( ) statement and then call it within my =IF( ) function? ...
=IF(A2<0.2,1,IF(A2<0.7,2,3))
...

What happened when you tried it?

If I were to use nested if for this, I would ask "of the 80% that do not result in 1, what percentage of that 80% returns 2"
Since you want 50 of the original population to result in 2, then you would want 50/80 or 62.5% of that 80% to return 2

I would use the formula =IF(RAND()<.2, 1, IF(RAND()<.625, 2, 3))

 

[GWBlack]

I copied my formula down 1000 cells and used =COUNTIF to develop a frequency distribution of the results. My results seem to be as expected. The first time, I got 210 values of 1, 492 values of 2, and 298 values of 3 (compared to the expected frequencies of 200, 500 and 300 respectively). Next time, I got 187 values of 1, 500 values of 2, and 313 values of 3. Next time, I got 203 values of 1, 483 values of 2, and 314 values of 3.