Download file:  Binary Chicks.zip.

This post is based on a recent discussion at Cosmic Horizons looking at numerical methods to solve the following problem:

The answer to the question is straightforward:

With a little thought, you should be able to realise that the answer is 25. For any particular chick, there are four potential out comes, each with equal probability. Either the chick is

• pecked from the left
• pecked from the right
• pecked from left and right
• not pecked at all

Only one of these options results in the chick being unpecked, and so the expected number of chicks unpecked in a circle of 100 is one quarter of this number, or 25.

But finding a theoretical solution to the distribution is not so simple.  A numerical simulation on the other hand can be set up in Python in a few lines of code.  Quoting Cosmic Horizons:

… if we treat a 0 as “chick pecks to the left”, and 1 as “check pecks to the right”, then if we choose a random integer between 0 and 2¹⁰⁰-1,  and represent it as a binary number, then that will be a random sampling of the pecking order (pecking order, get it!) As an example, all chicks peck to the left would be 100 0s in binary, whereas all the chicks peck to the right would be 100 1s in binary.

I have adapted the Python code provided at Cosmic Horizons so that it can be called from Excel ( via xlwings) as a user defined function (UDF).  My version also allows the number of chicks and the number of repeats to be passed as function arguments (updated 18 Jun 17, unused xx array removed):

from bitarray import bitarray
from random import randint
import numpy as np

def CountPecks(nchick, nruns):
    nmax = 2**nchick
    nstore = np.zeros(nchick)

    s1 = bitarray('001')
    s2 = bitarray('011')

    for n in range(0, nruns):
        nn = randint(0, nmax-1)
        a = bitarray( "{0:b}".format(nmax+nn) )

        a[0] = a[ len(a)-1 ]
        a.append(a[1])

        v1 = a.search(s1)
        v2 = a.search(s2)

        nindex = len(v1) + len(v2)
        nstore[nindex] = nstore[nindex] + 1

    return nstore

Results from Excel for 100 chicks are:

The UDF is entered in cell C6 (=py_CountPecks, C3, C4), then entered as an array function:

• Select the number of cells required for the output array (C6:C105).
• Press Edit (F2)
• Press Ctrl-Shift-Enter.

Having entered the function, the number of chicks and/or the number of repeats can be changed by entering the values in cells C3 and C4:

The function returns an array of length equal to the specified number of chicks, but note that the number of un-pecked chicks can never be more than half the total:

A similar algorithm can be set up directly on the spreadsheet (see screenshot below):

• Column B contains functions:  =RANDBETWEEN(0,1)
• Column C checks for sequences corresponding to an un-pecked chick, 0,x,1: =IF(AND(B4=0,B6=1),1,0)
• Cell C3 counts the total number of un-pecked: =SUM(C5:C104)

A short VBA routine then repeats the calculation the specified number of times, and stores the results in column E:

Sub CountPecks()
Dim num As Long, i As Long, Counta(1 To 100, 1 To 1) As Long, Val As Long

Application.ScreenUpdating = False
num = [NRuns1]
For i = 1 To num
    ActiveSheet.Calculate
    Val = [Unpecked1]
    Counta(Val, 1) = Counta(Val, 1) + 1
Next i

[Res_1] = Counta
Application.ScreenUpdating = True
End Sub

This is simple to set up, but much slower than the Python version, since the spreadsheet generates 100 separate single digit values, rather than a single 100 digit binary value, as used in the Python code.

The VBA performance can be improved by using a similar approach to the Python code, but it is limited by the Excel Dec2Bin function, which is limited to a maximum decimal value of 511, or 9 binary digits.  The screen-shot below shows how a long binary string can be generated with repeated use of this function (see the download file for details).

A further improvement in the VBA speed can be gained by using the DecToBin UDF (previously described here), which will work with a decimal value up to 2^31-1, or 31 binary digits. This allows the 100 digit binary value to be generated from 3 31 digit values, plus one 7 digit value (see rows 21 to 24 below and in the download file).

The examples shown above, plus full VBA and Python code, can be found in Binary Chicks.zip.

The Python functions requires Python and xlwings to be installed.  The easiest way to install both is with Anaconda Python.