Results 1 to 11 of 11

20050203, 11:05 #1
 Join Date
 Jun 2001
 Location
 Morden, Surrey, United Kingdom
 Posts
 1,838
 Thanks
 3
 Thanked 0 Times in 0 Posts
Punnett Squares?! (WinXP/ExcelXP)
Does anyone by any chance have an XL spreadsheet set up to create Punnett Squares, and if so would you mind posting a copy, pretty please? (if you don't know what they are, it's pretty safe to assume you don't have a suitable spreadsheet!).
If not, I need a little help in setting one up, please. Basically, a Punnett Square is a device used in genetics to predict the outcome of a particular gene combination (a mating, in animal breeding terms), and a very basic one, dealing with one gene, would look like this (ignore the dots, they're just to space it a bit):
<table border=1><td> </td><td>. A .</td><td>. a .</td><td>. A .</td><td>. AA .</td><td>. Aa .</td><td>. a .</td><td>. Aa .</td><td>. aa .</td></table>
One parents' gene is shown across the top (Aa) and the other down the side (also Aa in this case), and the possible combinations available fill the square.
This is quite simple for one gene, but if you imagine trying to calculate the possible combinations when using several genes, eg AABbcCddEEff and AabbCCDdeEFF(six pairs) it becomes far, far more complicated! On top of which, I want to be able to enter the two original combinations (AABbcCddEEff and AabbCCDdeEFF, in this example) in two cells at the top, and have formulae to calculate all the possible permutations of these in the square below ...
I got as far as a formulae for that looked something like this (assume the gene string is in cell B1):
=concatenate(mid(B1,1,1), mid(B1,3,1),mid(B1,5,1), mid(B1,7,1), mid(B1,9,1), mid(B1,11,1))
which would pick up the first of each pair, but roundabout there my eyes started glazing over at the idea of trying to work out all the possible permutations (1,3,5,7,9,11  1,3,5,7,9,12  1,3,5,7,10,11  1,3,5,7,10,12, etc etc) and enter them as the headings, then repeat this down the side  I'm not a mathematician, but I imagine the number of columns/rows needed is going to be quite a lot!
Can anyone help with a way to set this up in the most painless way possible? I'm not set in stone on the layout, in anything except that I need to be able to create the square and calculate percentages on the outcomes, eg with the very simple one above, 25% will be AA, 50% Aa and 25% aa ...
Does this make sense?! <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>Beryl M

20050203, 12:23 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
I haven't looked into the percentages, but the attached workbook contains a macro that will create the square based on two input cells.

20050203, 12:35 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
If Aa and aA are "identical"
To be consistent shouldn't you list the 2nd as (capitals first):
AabbCCDdEeFF
or is that different than:
AabbCCDdeEFF
Steve

20050203, 12:37 #4
 Join Date
 Jun 2001
 Location
 Morden, Surrey, United Kingdom
 Posts
 1,838
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
Thanks, Hans, but by my reckoning the resulting punnett square should be 64x64, so if this works (and I don't understand the VBA to know what you've done!) it's only done a fraction of the possible combinations ...?
Beryl M

20050203, 12:40 #5
 Join Date
 Jun 2001
 Location
 Morden, Surrey, United Kingdom
 Posts
 1,838
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
Aa and aA are identical, Steve, but I can't think of an easy way to tell XL that if mid(B4,1,1) = a and mid(A5,1,1) = A then result = Aa ... can you?
Next question, though ... is there a function to search a range of 64x64 cells and return the number of times "AA" appears, that I can then repeat with "Aa", "aA" (!) and "aa"? CountA isn't casesensitive, and Find won't search a range ...?Beryl M

20050203, 12:41 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
I omitted the duplication if you have AA, for example. Here is a version that produces the full 64 x 64 square.

20050203, 13:38 #7
 Join Date
 Jun 2001
 Location
 Morden, Surrey, United Kingdom
 Posts
 1,838
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
Thanks for that, Hans, it looks much better! I don't suppose you know the answer to the other question I asked, do you? I put in my reply to Steve so you probably haven't seen it 
Is there a function to search a range of 64x64 cells and return the number of times "AA" appears, that I can then repeat with "Aa", "aA" and "aa"? CountA isn't casesensitive, and Find won't search a range ...?Beryl M

20050203, 13:55 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
Like this? It uses InStr with the little used Compare argument set to vbBinaryCompare.

20050225, 10:53 #9
 Join Date
 Jun 2001
 Location
 Morden, Surrey, United Kingdom
 Posts
 1,838
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
Hi Hans, I am happily using this, but I now have a requirement to check an entire cell's contents (again using these pairs of genes) for casesensitve "identicalness" (!)  I've just been trying to see how I can modify your COUNTsensitive functions to do this, but I'm afraid it's defeated me! It must be possible to simplify it considerably since it's just checking two cells contents against each other (but case sensitive), but I can't see how ...!
Many thanks in advance!Beryl M

20050225, 11:07 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
The StrComp function by default performs a casesensitive string comparison.
StrComp(Range("A1"), Range("A2")) returns 0 if cells A1 and A2 are identical (casesensitive), and either 1 or 1 otherwise.

20050225, 13:16 #11
 Join Date
 Jun 2001
 Location
 Morden, Surrey, United Kingdom
 Posts
 1,838
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Punnett Squares?! (WinXP/ExcelXP)
Thanks, Hans, that was just enough for me to do it!
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>Beryl M