Modelling natural selection

**elefante** · 11-17-2008, 01:55 AM

Solved.

Hello, I have been trying to model natural selection in Excel with limited success.

Two parent bugs (generated randomly) have an offspring whose colour depends on the colour of the parent bugs. This works fine.

Then some of the offspring are eaten by predators. This part also works fine.

Then I need the remaining offspring to be randomly paired and have offspring. How can I do this (the random pairing of the remaining bugs). Is there a function the can randomly generate a the contents of a cell or something?

http://img91.imageshack.us/img91/9200/excelbugsyw9.jpg

In the above image you can see the column to the right has "gaps" which correspond to the eaten bugs. The remaining bugs in that column need to randomly pair up and have their own offspring.

Thanks.

**Richard Schollar** · 11-17-2008, 03:43 AM

Hi & Welcome to ExcelForum!

I don't know how you are assigning the colour to the bugs but you could use a Rand() formula to subsequently sort the bugs and pair them up.

=RAND()

in a cell will return a 'random' number between 0 and 1, if you place such a formula in adjacent cells to each bug you have remaining (and copy>Pastespecial>Values) then you could simply sort the remaining bugs and these 'random' values based on the random values (eg ascending) which will result in a pair up (random) of the remaining bugs.

Richard

**elefante** · 11-17-2008, 04:28 AM

Heres a few more details about my method:
The parent cells refer to a cell that contains the colour. This is calculated by the following process:

=INT(3*RAND())

In the adjacent cells:

=IF(A1005=0,"BLUE",C1005)

=IF(A1005=1,"GREEN",D1005)

=IF(A1005=2,"YELLOW"," ")

I.e: if 0 = Blue, if 1 = Green, and if 2 = yellow.

The offspring from F3:F1002 (partially shown in screenshot) are calculated using "IF" and "AND" functions.

The colour of the cells themselves use Excel's "Conditional Formatting". I.e: the cells change colour based on the contents.

Whether a bug is eaten or lives is determined by the following formula:

=IF(AND(F3="BLUE",OR(Z1005=0,Z1005=1,Z1005=2,Z1005=3,Z1005=4)),"BLUE",IF(AND(F3="YELLOW",AA1005=0),"YELLOW",IF(AND(F3="GREEN",OR(AB1005=0,AB1005=1)),"GREEN","")))

The "Z1005", "AA1005" and "AB1005" refer to cells that contain a INT(RAND() function. The probability of blue being eaten is greater than yellow etc.

I tried the solution you suggested but I don't know how I put the RAND function adjacent to cells that contain the remaining bugs because this changes every time the cells are recalculated (it is not static).

Thanks again, hope the additional info will help.

**Richard Schollar** · 11-17-2008, 05:51 AM

I've got you - you want it via formula and don't want any manual interaction in the sheet. What will you use the results of the pair up for? I presume it will be to let the bugs reproduce and go thru the lifecycle again (and again, and again and so on) so that you demonstrate ultimately that Blues are screwed?

The problem with that scenario (from an Excel perspective) is that you will have an awful lot of formulas dependent on previous results (and they could be very complicated formulas).

An alternative way would be to use some code to run the iterations (ie mate, reproduce, die). Can you post a sample workbook? i assume there isn't anything particularly confidential about this? If you are determined to go a formula route, the workbook will certainly help too.

Richard

**elefante** · 11-17-2008, 06:30 AM

I've got you - you want it via formula and don't want any manual interaction in the sheet. What will you use the results of the pair up for? I presume it will be to let the bugs reproduce and go thru the lifecycle again (and again, and again and so on) so that you demonstrate ultimately that Blues are screwed?

That's all correct. Once I manage to get this lot done, it will be easy to tweak the variables to show other possiblities.

I hope the workbook makes sense and works. Its from Excel 2007, if needed I can upload the compatibility one as well.
Biology natural selection bugs.xlsx

Thanks for your help and quick replies.