RAND() Function Within Nested IF Statement

GWBlack

New Member
Joined
Jul 16, 2017
Messages
7
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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:
Upvote 0
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?
 
Upvote 0
How about
=MATCH(RAND(), {0, .2, .7})

or more generically

=LOOKUP(RAND(), {0, 0.2, 0.7}, {"one", "two", "three"})
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
...
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))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top