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]

Actually, I just set up my formula in an alternate way using "AND()" in the middle as follows:

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

This version results in exactly the same number of 1's, 2's and 3's each time I run 1000 replications. So indeed it seems Excel evaluates the =IF( ) in a left-to-right fashion and, therefore, the "AND" part is not really needed.

 

[joeu2004]

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))

Yes. You are not "calling" it; you are referencing it.

The only problem before was that you called RAND() twice. As you yourself explained, that resulted in the use of two different and independent random values being used. In that case, the result was a conditional probability: the probability that one random value is less than 0.2 times the probability that a different and independent random value is less than 0.7.

But as you yourself explain below, when you reference the same random value, it is a "given" that if it is not less than 0.2, there is already a 20% probability that it is less than 0.7. So in the second part IF() expression, there is only a 50% probability that the same random value is between 0.2 and 0.7, and a remaining 30% probability that it is not less than 0.7.

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?

You are correct. In fact, that is true of other Excel functions that evaluate arguments conditionally. For example, CHOOSE evaluates only one of the remaining arguments to the right, depending on the result of the first argument. In fact, such Excel "functions" are not truly functions at all. Instead, it is just function-like syntax for expressing a selection operation, much like "+" is syntax for expressing a particular two-operator arithmetic operation.

It should be noted that this left-to-right evaluation applies only to Excel functions, not UDFs; and only to some Excel functions. For example, the following formula does not work as we might intend:

=SUMPRODUCT(ISNUMBER(A1:A10)*ISNUMBER(B1:B10)*(A1:A10+B1:B10))

The intent might be to evaluate A1:A10+B1:B10 only in rows that contain two numeric values, thereby avoiding a #VALUE error. But in fact, SUMPRODUCT simply creates an array of 1s and 0s based on the logic value of ISNUMBER(...)*ISNUMBER(...), then tries to create an array of numeric values based on the arithmetic A1:A10+B1:B10. If the latter encounters a text value that cannot be interpreted as a number, a #VALUE error results.

 

[joeu2004]

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

Geesh, talk about killing an ant with a sledgehammer! Why would you go to all that trouble of understanding the conditional probability when there is something so much more straight-forward like LOOKUP (or MATCH in limited situations)?

Consider the situation when there are more than 3 results: 11% for 3, 13% for 5, 17% for 7, 9% for 9, 3.3% for 11, etc. I don't even want to think of how to construct the chain of conditional probabilities, much less the overhead of calling RAND() so many times potentially. Simply write:

=LOOKUP(RAND(),{0.11,0.24,0.41,0.5,0.533,etc},{3,5,7,9,11,etc})

(And yes, at some point it might make good sense to put the array constants into a range of cells.)

And by the way, that is so much more succinct than calling RAND() in one cell, then creating a chain of nested IF() expressions that reference that cell. The depth of the nested IF() expression becomes a significant limitation when used in Excel 2003 or when saved to "xls" files.

Aren't we beating a dead horse here?

 

[GWBlack]

Thank you to everybody who replied today! That was very helpful.

Have a great rest of the weekend,
Gary

 

[joeu2004]

Errata (in red)....

Consider the situation when there are more than 3 results: 11% for 3, 13% for 5, 17% for 7, 9% for 9, 3.3% for 1, 12.5% for 13, etc. I don't even want to think of how to construct the chain of conditional probabilities, much less the overhead of calling RAND() so many times potentially. Simply write:
=LOOKUP(RAND(),{0.11,0.24,0.41,0.5,0.533,etc},{3,5,7,9,11,etc})

That is:

=LOOKUP(RAND(),{0,0.11,0.24,0.41,0.5,0.533,etc},{3,5,7,9,11,13,etc})