Over on WorldBuilding we like to play with science-fictiony themes, including "what if there were more that two sexes" (examples here, here, here). All good clean fun and, I thought, a chance for me to polish my spreadsheet skills (I use Excel 2003).
I built a spreadsheet for a Monte Carlo model of three sexes using X and Y chromosomes: Male (YY), Female (XX) and Hermaphrodite (XY), using ordinary Mendelian inheritance. Thus M-F will always produce H, M-H will produce 50% M and 50% H, F-H similar, and H-H will produce 25% M, 25% F and 50% H.
I made a population of 200, breeding for 256 generations, and the results look reasonable, except for one thing. I had expected the population to stabilise at 50% H and 25% each of M and F, but I consistently get about 54% H and only 23% M and F. This does not seem to be an Excel bug, I get similar results with LibreOffice, nor is it affected by the sex of the "buffer" parents (see below) or the initial distibution.
The spreadsheet is available here (my personal website, usual disclaimers of course) and I have documented it below. It looks as if my random functions
CHOOSE(INT(4*RAND()+1) are not returning 50% and 25% as intended but I don't know how to test that directly. Am I doing something wrong?
4 so that I can simply add the two parents. Most of the cells (B6:IV205) calculate the next generation and contain (this is cell B6):
=CHOOSE (A5+A7, // sum of parents -99, // 1 can't happen A6, // 2 = MM = don't breed, copy old 4, // 3 = MF -> H A6, // 4 = FF = don't breed, copy old IF(RAND()>0.5,1,4), // 5 = MH -> M or H IF(RAND()>0.5,2,4), // 6 = FH -> F or H -99, // 7 can't happen CHOOSE(INT(4*RAND()+1),1,2,4,4) // 8 = HH -> M or F or H or H )
The first row of Sheet1 shows a generation count, the second, third and fourth the number of each "sex" in that generation (divided by 2 to give a percentage of the population of 200). Rows 5 and 206 provide a (buffer) parent for rows 6 and 205. Column A builds an initial population based on the value in A2.
I use conditional formatting to colour Males as blue, Females as pink and Hermaphrodites as green; with 25% zoom and a column width of 3 I can see the whole range on my monitor. On Sheet2 I plot the populations using rows 2-3-4 of Sheet1.
Details of all cells on Sheet1:
A1 "gen1" B1:IV1 ="g"&COLUMN() // column title A2 50% // initial hermaphrodite percentage B2:IV2 =COUNTIF(B6:B205,"=4")/2 // hermaphrodite percentage A3 ="M:"&COUNTIF(A6:A205,"=1")/2 B3:IV3 =COUNTIF(B6:B205,"=1")/2 // male percentage A4 ="F:"&COUNTIF(A6:A205,"=2")/2 B4:IV4 =COUNTIF(B6:B205,"=2")/2 // female percentage A5 4 // lower buffer parent B5:IV5 =A5 // echo buffer A6:A205 =IF(RAND()>$A$2,IF(RAND()>0.5,1,2),4) // initial population B6:IV205 =CHOOSE(A5+A7,-99,A6,4,A6,IF(RAND()>0.5,1,4),IF(RAND()>0.5,2,4),-99,CHOOSE(INT(4*RAND()+1),1,2,4,4)) A206 4 // upper buffer parent B206:IV206 =A206 // echo buffer
Of course I'm open to advice on all aspects of my opus.