Смещенные случайные результаты в Excel

Question

На WorldBuilding нам нравится играть на научно-фантастические темы, в том числе «что, если бы было больше двух полов» (примеры здесь, здесь, здесь). Все хорошо и весело, и я подумал, что у меня есть шанс отточить свои навыки работы с электронными таблицами (я использую Excel 2003).

Я построил таблицу для модели Монте - Карло из трех полов , использующих X и Y хромосом: M эля (YY), F emale (XX) и H ermaphrodite (XY), используя обычный Менделя наследования. Таким образом, MF всегда будет производить H, MH будет производить 50% M и 50% H, FH аналогично, а HH будет производить 25% M, 25% F и 50% H.

Я сделал популяцию в 200, размножаясь в течение 256 поколений, и результаты выглядят разумными, за исключением одного. Я ожидал, что популяция стабилизируется на 50% Н и 25% каждого из М и F, но я последовательно получаю около 54% Н и только 23% М и F. Это не похоже на ошибку Excel, я получаю аналогичные результаты с LibreOffice, а также не зависит от пола "буферных" родителей (см. ниже) или начального распределения.

Электронная таблица доступна здесь (мой личный веб-сайт, конечно же, отказ от ответственности), и я задокументировал это ниже. Похоже, что мои случайные функции IF(RAND()>0.5 и CHOOSE(INT(4*RAND()+1) не возвращают 50% и 25%, как предполагалось, но я не знаю, как это проверить напрямую. Я делаю что-то неправильно?

Детали кода

Я кодирую M ale как 1 , F emale как 2 и H ermaphrodite как 4 чтобы я мог просто добавить двух родителей. Большинство ячеек (B6:IV205) рассчитывают следующее поколение и содержат (это ячейка 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  
 )  

В первой строке листа Sheet1 показано количество поколений, во втором, третьем и четвертом - число каждого "пола" в этом поколении (деленное на 2, чтобы получить процент населения в 200 человек). Строки 5 и 206 предоставляют (буферный) родитель для строк 6 и 205. Столбец A строит начальную популяцию на основе значения в A2.

Я использую условное форматирование, чтобы окрасить самцов в синий цвет, самок - в розовый, а гермафродитов - в зеленый; с увеличением 25% и шириной столбца 3 я могу видеть весь диапазон на моем мониторе. На Листе 2 я строю популяции, используя строки 2-3-4 Листа1.

Детали всех ячеек на Листе 1:

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  

Конечно, я открыт для советов по всем аспектам моего опуса.

Answer 1

Я подозреваю, что это не ошибка в Excel, но это правильно (по крайней мере, настолько правильно, насколько это может быть основано на случайной случайности.)

Причина этого не в случайности, а в вашей вероятности в этом расчете:

=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  
 )  

В частности, ваша самая последняя ситуация, HH -> M or F or H or H Это означает, что у вас есть 50% шанс получить H по сравнению с 25% каждый для M или F, если мы будем использовать результат HH . Таким образом, следует ожидать перекоса в сторону H над M или F.

Я подозреваю, что если вы измените эту часть на

CHOOSE(INT(3*RAND()+1),1,2,4)

вы бы увидели вероятность, которую вы ожидали, но не обязательно, что правильно (я на самом деле не сделал математику.)