Genetics with Excel: Determining ratios from Punnett Square Crosses
 

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.

Genetics with Excel: Determining ratios from Punnett Square Crosses
 

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

Genetics with Excel: Determining ratios from Punnett Square Crosses
 

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

"Brett" wrote in message
...
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.