# Mutant Cell Modeling Problem [Homework]

Posted on December 10th, 2010 in Learn Excel - 22 comments

Today’s homework is inspired from an email I got from Simran,

I have a mathematical problem, I do not know if it can be solved using excel. So, the question is what is the probability of selecting a mutant cell if the percentage of mutant in the whole population is 0.01 and each cell is dividing 4 times per day,meaning at 2 raised to the power of 4 and the population is say 100,000 and is growing exponentially for 10 days and every day you are selecting, say 1/16 th of the population for the next growth? So, after 10 days, what are the chances that I have different mutants and not clone of the same mutant?

Let me break down the problem for you.

1. We have a starting population of 100,000 cells (not the A1 kind)
2. Out of which 1% are Mutant cells
3. Each cell becomes 16 cells by end of day
4. Naturally, there will be 1 new mutant cell for every 100 new cells produced in such a way.
5. At the end of day, we just pick 1/16th of the cells and go for the next day

At the end of 10 days, what portion of total cells are,

• Normal (ie no mutation at all)?
• Clones of original Mutant cells?
• New Mutants?

### What you need to do?

Simple. Make an Excel Model to simulate the cell mutation. Your objective is to find answers to above questions thru that model. (Obviously, we can find answers without modeling, but I want to see how we can creatively use Excel to solve a problem like this.)

### Once you find a solution,

• You can also email to me at chandoo.d @ gmail.com with subject “mutant ninja turtle”

### What if you don’t find mutant cells exciting?

Here is a mutant teddy bear. Enjoy it. (source)

### More Excel Homework

If you enjoy excel challenges, then you will love Excel Homework page. It has a bunch of challenging exercises for you.

All the best. Go muse over the mutation problem.

 Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download] Excel Links – Next Dashboard Contest Edition
 Written by Chandoo Tags: homework, Learn Excel, mathematics, Microsoft Excel Formulas, modeling Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 22 Responses to “Mutant Cell Modeling Problem [Homework]”

1. dan l says:

The game of life in excel?

2. Simran says:

I am not getting any response π

3. Hui... says:

I Think the answer is here

4. Simran says:

I am deeply distressed by "no response" I am genuinely looking for a solution and have tried to use my mathematical skills too, which are not that bad, I love Maths, but ......It seems nobody is serious about my problem π I am not finding a crying smilie π

5. Robapottamus says:

Hi Simran

Hope you can find it, and hope it helps!

6. Simran says:

Hey Rob,
Its great, I can change the % mutations and find answer for variations in muatant populations too and that too easily, great.
Thankyou
π Finally smiling smilie
Regards
Simran

7. Robapottamus says:

All good Simran, glad i could help. Yes, all the assumptions are changeable, and you can also change your starting population in Cell C9.

Also it's easy to add further years, simply by dragging the formulas in Column M across.

There is probably a way to get multiple years calculated in one cell, but that is a bit complicated for me, plus i like to see each year's iteration as a good way of sense checking my calcs.

cheers
Rob

8. Fred says:

I sent my version of the answer to Chandoo. May be you can go to him and see my take on the homework. π

9. Simran says:

Fred,

There seems to be some problem with your response. If I assume that there is only 1 mutant, then I don't have to worry wbout probability, because whatever mutation I am getting is going to be from the single starting mutant. In this case, the probability is overall mutants observed divided by the total no. of cells in the population.

But when I have more than 1 mutant and then I am selecting a small portion of that population (1/16). Thus, they are further dividing and the number again increases as a multiple of 16 (no. of mutant * 16) and again doing teh same process of taking forward a smallpopulation for further growth and letting them again multiply 16 times, so the probability of selecting different mutant and not progeny of the same mutant. What is this probability? I am interested in this.

Rob,
You did not multiply the mutant popultaion by 16, as they are also growing at the same rate. There can be no way that by the end of 10 days, with 1% mutant popultaion, they reach around 45.5% at the end of day 10. It will be much much smaller number, I am trying to figure out this too.

Oops, not that easy.
But anyways, thankyou Fred and Rob, you guys atleast try, I do not even try others problems!!!
Regards
Simran

10. Fred says:

Simran,
you hv mistaken the table I had built. it was merely meant for demonstrating how I define the definition of the 2nd question: how many muntant clone cells were created by the original "batch" of mutants at day 10. Since at day 10 the original "batch" would still be 1 and 15 "batches" were the result. the table isn't really meant for calculating how the mutant cells proliferate. And the table can also to understand how many mutants were created by which generations of mutants. π

11. Robapottamus says:

Hi Simran
Thans for pointing that out! I have sent you an email with a corrected model. The proportion calculation was dividing by a incorrect cell. hope that helps!
cheers
Rob

12. Simran says:

Thankyou Rob,
Now I have my numbers......I love this, so now I can say it looks like I have a solution.
Great, thankyou...:)
Thankyou Fred, I got your point too....
Thankyou once again..
Simran

13. Lynda says:

Am I the only one who looked at the title & wondered how you get the cells in a spreadsheet to mutate in the first place?

14. Simran says:

Lynda,
The cells are not in the spreadsheet, but in culture and they got mutated as they were artificially induced for getting a mutation. There are different ways to do it, as an example, you can use chemical mutagen or you can use UV (Ultra Violet) rays to induce mutations. So, my question was based on this. π

15. Nik says:

Dear Simran,

I'd like to point out that the model offered by Fred and Rob (which indicates that at day 10, there should be 0.43% mutants, if you take into account the correct divisor) does not take into account the effect of randomized selection at the end of each day. Depending on how you select your 100.000 cells out of the 1.600.000, you can change the result of "0.43%" quite dramatically. For example, each time you scoop, by hazard, you could be scooping ALL the mutant cells, increasing the number of mutant cells at day 10 dramatically.

If this is not purely a mathematical question, and that you cannot assume that at each day, you will scoop a representative sample, your model becomes probabilistic, and you need to calculate the distribution of probabilities for each outcome. This is relatively easy to do through the use of factorials if you want to calculate one specific probability, but unwieldy if you'd like to calculate all probabilities. However, fear not: there are excel tools to make this work easier, such as Oracle's Crystal Ball.

If it is purely a mathematical problem, I hope this satisfies your curiosity. If it really is a research issue (for example, if you're looking at how many mutations some type of UV light are causing), then I really recommend that you model this, as it might significantly alter your research conclusions.

16. Simran says:

Dear Fred,
I wanted to calculate the probability of getting a mutation, there are other variables, ofcourse, which make it even more complicated. The cells in culture are not undergoing any selection, and it is going to be a random selection. So there is no way of scooping the mutant cells, although I wish I could do that somehow. On the contrary, every time, u are diluting the culture, you are somehow diluting the probability of getting independent mutants.
This was purely research based scientific problem and I have to find the mutant, I wanted to know what would be the decent number to go for screening. The efficiency of UV induced mutation is also not known. Thus, it only increases the number to be screened.

Simran

17. Simran says:

Nik,
I sent my response to your comment and addressed it to Fred, sorry for this blunder. It seems my thought process is also getting mutated by the thought of screening the huge number π

18. Coforfe says:

Although it is a little late, I have just left a possible solution file in Skydrive.
Name of the file: "Mutant-Cell - 2010-Dec".
User: Carlos Ortega.

Hope it helps.

Regards,
Carlos.

19. Coforfe says:

Sorry, I forgo the url.

20. ChrisU says:

I know that I am a bit late to the game, but here's my attempt. (NOTE: I used 1000 cells rather than 100,000 for simplicity and speed).
There's two ways to get a solution. The mathematical solution (which has no randomness) builds up to a 10.47% mutant population after day 10. Then I set up a simulation that uses random numbers to do the selection randomly, so that it is not proportional. In this file, you can run up to 30 trials in the simulation and compare the results to the mathematical (expected) solution and I found that it could vary up to 5% on either side (so that the mutants end up as 5-15% of the total population on day 10).

My math results are different from Coforfe-- at least one reason is that the end of day amount should always be the same as the original population. The amount of mutants should increase each day as the originals maintain their population and new ones are created, so there is an expected daily increase.

Feedback?

21. Simran says:

Dear All,
Wish you a great, joyous, happy and prosperous New Year with best wishes and happiness to u all.
Dear Carlos and Chris,
Please accept my apologies for not going through your solutions. And I cannot even promise that I will find time to go through it as it is very hectic schedulde and I am not getting any sleep and what are holidays!!!
So, may be sometime ter 20 days or so, I will have time to study your solutions.
Love you all for helping me.....
π
Dear Chandoo, you are great.
Love to u and your family too. π

22. […] (Here’s the full challenge) […]

 Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download] Excel Links – Next Dashboard Contest Edition