# RAND() Function Within Nested IF Statement

#### GWBlack

##### New Member
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

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

#### Rick Rothstein

##### MrExcel MVP
You could get that randomized distribution percentages like this...

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

Last edited:
• GWBlack

#### GWBlack

##### New Member
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

##### MrExcel MVP
=MATCH(RAND(), {0, .2, .7})

or more generically

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

Last edited:
• GWBlack

#### joeu2004

##### Well-known Member
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.

=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.

Last edited:
• GWBlack

#### Tetra201

##### MrExcel MVP
Or

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

• GWBlack

#### GWBlack

##### New Member
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

##### MrExcel MVP
...
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

#### GWBlack

##### New Member
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.

1,082,139
Messages
5,363,363
Members
400,731
Latest member
Jackserver

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...