return 0 or 1 randomly with prescribed frequencies

[derekj72]

Hi, could you please help me with a function that returns 0 70% of the time and 1 30% of the time?

 

[bbott]

Same thing. There is nothing sacrosanct about "--". Any arithmetic operation with TRUE and FALSE convert them to numeric values 1 and 0. For example, ("a"="A")+3 returns 4 because ("a"="A") is TRUE in Excel, the arithmetic operation "+" converts TRUE to 1, and 1+3 is 4.

Back to "--".... Many people mistakenly call it a double-negative "operator". It is simply two unary minuses ("double negative"). --5 is 5 because -5 is negative 5, and -(-5) is positive 5. Likewise, --TRUE is 1 because -TRUE is -1, and -(-1) is 1.

In this case, it's actually not the same thing. Since the *1 is within the expression being evaluated, no coercion is taking place. This:

=RAND()*1>0.7

still evaluates to TRUE/FALSE. It would need to be written as:

=(RAND()>0.7)*1

But the point is really moot since it's wrapped in an IF statement with TRUE/FALSE arguments....