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
 

GWBlack

New Member
Joined
Jul 16, 2017
Messages
7
ON EDIT: I should have added an extra closing (right) parenthesis in my IF statement.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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

New Member
Joined
Jul 16, 2017
Messages
7
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
Joined
Jan 15, 2007
Messages
22,797
How about
=MATCH(RAND(), {0, .2, .7})

or more generically

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

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,582
Office Version
2010
Platform
Windows
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:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
Or

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

GWBlack

New Member
Joined
Jul 16, 2017
Messages
7
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
Joined
Jan 15, 2007
Messages
22,797
...
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

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

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top