Genetics with Excel: Determining ratios from Punnett Square Crosses

[Brett]

Excel may not be the best program for working with trihybrid crosses (3
traits), tetrahybrid crosses (4 traits), and so on, but I have been able to
create a formula to fill in a monohybrid cross (1 trait) all the way to a
cross with 5 traits.



My problem lies with finding ratios for genotypes (allele combinations; e.g.
AaBbCCdd) and phenotypes (dominant in the presence of 'A,' recessive in the
presence of 'a' alone) using functions/formulae. In either case, the
function COUNTIF seems to be required, but Excel is not case sensitive. A
case sensitive function is required for determining the difference between
all dominant alleles (AABBCCDD) versus all recessive (aabbccdd).



The monohybrid cross is as follows:

A a

A| AA Aa

a | Aa aa



The phenotypic ratio would be 3:1 (3 containing the dominant 'A,' 1
containing no dominant 'A'). The genotypic ratio would be 1:2:1 (1 AA, 2
Aa, 1 aa).



The dihybrid cross is as follows:

AB Ab aB ab

AB| AABB AABb AaBB AaBb

Ab| AABb AAbb AaBb Aabb

aB| AaBB AaBb aaBB aaBb

ab| AaBb Aabb aaBb aabb



The phenotypic ratio is 9 (A_B_):3 (A_b_):3 (a_B_):1 (a_b_) (notice how the
first letter of each pair determines how it's categorized). The genotypic
ratio is 1:2:1 (like the monohybrid):2:4:2 (lower left and upper right
squares are the same):1:2:1 (like another monohybrid).



When it reaches the cross with 5 traits (A, B, C, D, and E), there are 1024
squares, and that would be a pain to count regarding both ratios.



Any help with devising accurate shortcuts with counting either of the two,
or both, ratios would be greatly appreciated.



Thanks.

 

[Ken Johnson]

HiBrett,

You stated "but Excel is not case sensitive"
The info on the FIND worksheet function states that this function is
case sensitive.

Ken Johnson

 

[Biff]

Hi!

In general, there are several ways to count based on case.

I can get these on the monohybrid cross:

The phenotypic ratio would be 3:1
The genotypic ratio would be 1:2:1

And, I can get this on the The dihybrid cross:

The phenotypic ratio is 9 (A_B_):3 (A_b_):3 (a_B_):1 (a_b_)

And this should be "doable" if it's anything like "The phenotypic ratio is 9
(A_B_):3 (A_b_):3 (a_B_):1 (a_b_)"

the cross with 5 traits (A, B, C, D, and E), there are 1024 squares

Can you send me a *SMALL* (<1MB) sample file that shows how this data is
structured? If so, let me know how to contact you.

Biff