Excel Simulations

Gerard M. Verschuuren

• Publisher: Holy Macro! Books
• Year: 2013
• Print ISBN: 9781615470228
• eBook ISBN: 9781615473359
• Edition: 1
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook. The simulations covered offer a fun alternative to the usual Excel topics and include situations such as roulette, password cracking, sex determination, population growth, and traffic patterns, among many others.

Genetics

20. Chromosomes

What the simulation does

Open file 3-Genetics.xlsx on sheet “Chromosomes.” This simulation just shows what the probability is that an individual still has chromosomes derived from one particular grandparent. Since we have 23 pairs of chromosomes, it is most likely that we have 11 or 12 chromosomes that were handed down to us from one particular grandparent, two generations ago—actually a 16% chance. But the outcome can vary between no chromosomes at all or all 23 chromosomes together—but the extremes are very unlikely (see columns M and N).

The basic idea is that parents randomly contribute part of their genetic material—chromosomes, genes, and DNA—to their children (and grandchildren, etc.). As a consequence, genetics, the science of inheritance of traits and characteristics, is modeled probabilistically.

What you need to know

Humans have a double set of 23 chromosomes. Their egg cells or sperm cells contain only a single set of 23 chromosomes. After fertilization and conception, the new human being has 23 double sets again—one set from one parent and one set from the other parent.

From these 23 pairs, one set goes to the next generation again, but it is undetermined whether individual chromosomes are from grandfather’s side or grandmother’s side. So the new generation could have as little as 0 chromosomes or as many as 23 chromosomes from one of the four grandparents.

As an aside, the situation is much more complicated. One problem is that chromosomes do not remain identical during the formation of reproductive cells, but they can exchange parts between the two of a pair—which is called crossing-over or recombination. On this sheet, we stay clear of that issue.

In addition to the function BINOMDIST (see Simulation 9), we will also use the new function BINOM.INV in this simulation. There is no pre-2010 version of this function, so if you use a file with this function in 2007, you will get an error message. In Excel 2007, an alternative would be CRITBINOM (see Simulation 19).

What you need to do

1. Place in cell B2:B1001: =BINOMDIST(A2,23,0.5,0)/100. BINOMDIST needs to know the number of “successes” (running from 0 to 23 in column A), out of 23 trials (23 chromosomes), with a 50% probability of “success” in each trial, and with a non-cumulative setting in our case. Divide by 100.

2. Place in cells I2:I1001: =BINOM.INV(23,0.5,RAND()). The last argument is a criterion value (alpha) between 0 and 1.

3. Place in cell L2: =AVERAGE(I:I).

4. Place in cell M2: =COUNTIF(I:I,”<3”).

5. Place in cell N2: =COUNTIF(I:I,”>20”).

6. Select K2:N22 and start a Data Table with no row input and an empty cell (say, K1) as column input: {=TABLE(,K1)}.

7. Give range M2:N22 conditional formatting: Cell Value > 0.

8. When the sheet recalculates (Sh F9), notice how rare it is that descendants have received less-than-3 or more-than-20 chromosomes from a grandparent (in columns M and N).

21. Sex Determination

What the simulation does

Open file 3-Genetics.xlsx on sheet “SexDetermination.” This sheet simulates what happens when a father (XY) and a mother (XX) have one descendant, who has in turn another descendant, and so forth. It is like a family tree.

If the descendant is a female (XX), that cell gets marked with a color. If the descendant still has the original Y-chromosome (Y’) from the (great-great-grand-) father, that chromosome is marked with an apostrophe and flagged on the left side of each descendant. In the figure below, there happen to be two female descendants, and the ancestral Y-chromosome got “lost” by chance after two generations.

What you need to know

One of the 23 pairs of chromosomes is called the sex-chromosome pair. It either holds two similar chromosomes (XX) or two unalike chromosomes (XY; Y is actually very short ). The presence of the Y-chromosome determines maleness.

The father (XY) produces sperm cells with either an X-chromosome (50% chance) or a Y-chromosome (50% chance). If the egg cell—which has always one X-chromosome—is fertilized by a sperm cell with a Y-chromosome, the descendant will be a male. So there is a 50% chance for either a male or a female descendant (in reality, there is a slight difference, though).

What you need to do

1. Place in cell D3 the following formula:
=IF(RAND()>0.5,”XX”,IF(C1=”XY’”,”XY’”,”XY”)). Do not forget some single apostrophes (‘) inside the double quotes.

2. This formula creates a 50% chance for either a male or a female. If it is a male, we check whether the father had an ancestral Y-chromosome; if so, this male inherits it.

3. Copy this ...

Table of contents

• Gambling
• Statistics
• Genetics
• Financial
• Expansion
• Monte Carlo Simulations
• Iterations
• Extras
• Appendices