return 0 or 1 randomly with prescribed frequencies

derekj72

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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I don't know how to test for your frequencies exactly, but this was my idea:

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

=--(rand() >= 0.7)

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

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

=--(rand() >= 0.7)

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

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

Last edited:
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.

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.

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.

Replies
0
Views
84
Replies
0
Views
183
Replies
2
Views
100
Replies
2
Views
90
Replies
18
Views
182

1,203,524
Messages
6,055,907
Members
444,832
Latest member
bgunnett8

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.

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

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