Results 1 to 11 of 11
  1. #1
    Silver Lounger
    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


  2. #2
    Plutonium Lounger
    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    Silver Lounger
    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


  5. #5
    Silver Lounger
    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 case-sensitive, and Find won't search a range ...?
    Beryl M


  6. #6
    Plutonium Lounger
    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.

  7. #7
    Silver Lounger
    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 case-sensitive, and Find won't search a range ...?
    Beryl M


  8. #8
    Plutonium Lounger
    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.

  9. #9
    Silver Lounger
    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 case-sensitve "identical-ness" (!) - 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


  10. #10
    Plutonium Lounger
    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 case-sensitive string comparison.

    StrComp(Range("A1"), Range("A2")) returns 0 if cells A1 and A2 are identical (case-sensitive), and either 1 or -1 otherwise.

  11. #11
    Silver Lounger
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •