Assign Gender with 50% Frequency

Does it need to be exactly 10 and 10 or approximately 50%. If approximately, your formula should do it.

I am trying to figure out this formula but the one problem I am encountering is this being a circular reference.

The formula I'm using here is counting the intances of Male:Female and trying to adjust it as it goes to ensure the amount of male's to female's remains equal. This is based off of a Random Chance in Cell B1 and then going from there, only picking at random again if the # Male's = # Female's. The problem with Random is that it changes each time new data is entered anywhere in the sheet and once again changes the values in the cells.. So this may not solve the problem, but it may give others some ideas to get us towards the final solution...



=IF(COUNTIF(B:B,"Male")=COUNTIF(B:B,"Female"),IF(RANDBETWEEN(1,2)=1,"Male","Female"),IF(COUNTIF(B:B,"Male")>COUNTIF(B:B,"Female"),"Female","Male"))

@ Vamosj
Maybe don't use the whole column as a reference (this is giving you the circular reference), try instead to get it to read like this, B$1:B1 when your are in B2 ( this will check the previous cells, for the counts, then decide which formula to use ?

Hi,

I'm afraid the only true method to ensure a genuinely random selection here would be to enter your formula into 20 cells, use an additional cell to monitor the ratio of Males to Females and then iterate enough times until that cell shows 50%.

Solutions whose calculations for later cells are in some way influenced by results in preceding cells are by definition not random.

Regards

	B	C
1	Gender
2	M	B2: =CHOOSE(SIGN(COUNTIF(B$1:B1, "M") - COUNTIF(B$1:B1, "F")) + 2, "M", CHOOSE(RANDBETWEEN(1,2), "M","F"), "F")
3	F
4	F
5	M
6	M
7	F
8	M
9	F
10	F
11	M
12	M
13	F
14	M
15	F
16	F
17	M
18	M
19	F
20	M
21	F

That doesn't generate a very random sequence. It would be better to list 10 M and 10 F, add a random column next to it, and sort by that.

Ooh, this is much better:

	B	C
1	Gender
2	F	B2: =IF(RANDBETWEEN(COUNTIF(B$1:B1, "M"), 19 - COUNTIF(B$1:B1, "F")) > 9, "F", "M")
3	F
4	M
5	M
6	M
7	M
8	F
9	M
10	F
11	M
12	M
13	M
14	F
15	F
16	M
17	F
18	M
19	F
20	F
21	F