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?

 

[Scott Huish]

I don't know how to test for your frequencies exactly, but this was my idea:

=MID(1000100100,RANDBETWEEN(1,10),1)

 

[shg]

=--(rand() >= 0.7)

 

[bbott]

=choose(randbetween(1,10),0,0,0,0,0,0,0,1,1,1)

 

[SpillerBD]

Or....
=IF(RAND()*1>0.7,0,1)

 

[SpillerBD]

=--(rand() >= 0.7)

That's sneaky but some might get lost on what the "--" is for.

 

[Scott Huish]

I'm a little lost on why you multiplied by 1 ??

However, in explanation of the --, it coerces TRUE values to 1 and FALSE values to 0

 

[SpillerBD]

I'm a little lost on why you multiplied by 1 ??

Old habits die hard. I always indicate the upper bound value explicitly, even when 1.

 

[joeu2004]

I'm a little lost on why you multiplied by 1 ??
However, in explanation of the --, it coerces TRUE values to 1 and FALSE values to 0

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.

 

[Scott Huish]

I don't think anyone said anything was sacrosanct. However there have been discussions about the speed of operations depending on which mathematical operator you use and -- is reportedly the most efficient.