Hi to all i have data like this,
DATA HAVE
pop A B C D E
P1 T/T C/C C/C T/T C/C
P2 A/A G/G C/C T/T C/C
1 A/A G/G C/C T/T C/C
2 A/A G/G C/C T/T C/C
3 A/T A/C A/G A/T A/C
4 T/A T/G T/C T/A T/G
5 G/A G/T G/C G/A G/T
6 C/A C/T C/G C/A C/T
i want to do the following steps
1. first I want to convert all A/A to A, T/T to T, C/C to C, G/G to G, Z/Z to - and -/- to - and remaining characters with combination of A,T,G,C like A/T,G/T,C/G,T/C etc to H for this i am using this nested IF formula =IF(E3="A/A","A",IF(E3="T/T","T",IF(E3="G/G","G",IF(E3="C/C","C",IF(E3="Z/Z","-",IF(E3="-/-","-","H")))))).
2. Now I want to know status from A to E by comparing P1 with P2, if P1=P2 then status from A to E is mono or any one of P1 or P2 contains Z/Z or -/- then status from A to E is mono else status from A to E is poly, i am using this formula =IF(P2=P3,"mono",IF(OR(P2="Z/Z",P2="-/-"),"mono",IF(OR(P3="Z/Z",P3="-/-"),"mono","poly"))).
3. I want to match 1 in pop column with P2 in pop column for A to E, if 1 in pop column matches to p2 in pop column and its status is poly only then I would like to give 1 otherwise as such, if it is mono I do not want to do anything. now i am using this formula for this IF(DT4=DT$3,DT$2="POLY"),1,DT4).4. Now I will calculate # 1's and # H's i am using countif funtion for this.
5. finally I will calculate %sim with this formula =((#1*2+#H)/((#1+#H)*2))*100.
Now i need help AT 3RD STEP and presently i am using this formula =IF(AND(E4=E$2, E$3="poly"), 1, E4) and it is giving E4 (value if false) not 1 (value if true). I really need help on this, any help would be highly appreciated. I am attaching example data file here for better understand.
Thanks i advance
Genetist
Bookmarks