Generate Random Value Based On Assigned Probability Per Group

[nfr03]

Hi everyone. I would need help in creating a formula to populate a random value of 1 or 0 based on a percentage value assigned per group.

Suppose my data looks something like this. I need to populate the third column with either 1 or 0 on whether that person is part of the sample or not.

PersonID	Region	Selection Value (1 or 0)
1​	A
2​	B
3​	B
4​	C
5​	A

The basis of sample selection is based on a lookup table similar to below. Per region I have a probability for selection.

So for instance 2% of people in Region A would have a value of 1.
98% of people in Region A would have a value of 0.

1% of people in Region B would have a value of 1.
99% of people in Region B would have a value of 0.

And so on....

Region	Percentage to Select (1)	Percentage not to Select (0)
A	2%​	98%​
B	1%​	99%​
C	8%​	92%​

Would appreciate any help as I am stuck as of the moment with this.
Thanks a lot!

 

[Eric W]

Welcome to the MrExcel forum!

How about:

Book2
	A	B	C	D	E	F	G	H	I
1	PersonID	Region	Selection Value (1 or 0)				Region	Percentage to Select (1)	Percentage not to Select (0)
2	1	A	0				A	2%	98%
3	2	B	1				B	1%	99%
4	3	B	0				C	8%	92%
5	4	C	1
6	5	A	0
7
Sheet9

Cell Formulas
Range	Formula
I2:I4	I2	=1-H2
C2:C6	C2	=(RAND()<VLOOKUP(B2,$G$2:$H$4,2,0))+0

 

[nfr03]

Welcome to the MrExcel forum!

How about:

Book2
	A	B	C	D	E	F	G	H	I
1	PersonID	Region	Selection Value (1 or 0)				Region	Percentage to Select (1)	Percentage not to Select (0)
2	1	A	0				A	2%	98%
3	2	B	1				B	1%	99%
4	3	B	0				C	8%	92%
5	4	C	1
6	5	A	0
7
Sheet9

Cell Formulas
Range	Formula
I2:I4	I2	=1-H2
C2:C6	C2	=(RAND()<VLOOKUP(B2,$G$2:$H$4,2,0))+0

Nice. This works exactly as intended. Thank you so much, Eric! ?

 

[Eric W]

Happy to help! ?