Excel Workbook | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | |||

1 | 02 | 12 | 17 | 21 | 36 | #N/A | C | S | C | S | ||

2 | 11 | 21 | 32 | 33 | 36 | D | C | C | D | S | ||

3 | 11 | 16 | 18 | 22 | 30 | D | S | S | D | S | ||

4 | 03 | 07 | 10 | 26 | 36 | #N/A | #N/A | C | S | S | ||

Sheet2 |

**Excel 2007**

Cell Formulas | ||
---|---|---|

Range | Formula | |

F1 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(A1,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(A1,{1,2},1)+0)+{0,1},MID(A1,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(A1,{1,2},1)+0,MID(A1,{1,2},1)+0)))) | |

F2 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(A2,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(A2,{1,2},1)+0)+{0,1},MID(A2,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(A2,{1,2},1)+0,MID(A2,{1,2},1)+0)))) | |

F3 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(A3,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(A3,{1,2},1)+0)+{0,1},MID(A3,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(A3,{1,2},1)+0,MID(A3,{1,2},1)+0)))) | |

F4 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(A4,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(A4,{1,2},1)+0)+{0,1},MID(A4,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(A4,{1,2},1)+0,MID(A4,{1,2},1)+0)))) | |

G1 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(B1,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(B1,{1,2},1)+0)+{0,1},MID(B1,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(B1,{1,2},1)+0,MID(B1,{1,2},1)+0)))) | |

G2 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(B2,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(B2,{1,2},1)+0)+{0,1},MID(B2,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(B2,{1,2},1)+0,MID(B2,{1,2},1)+0)))) | |

G3 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(B3,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(B3,{1,2},1)+0)+{0,1},MID(B3,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(B3,{1,2},1)+0,MID(B3,{1,2},1)+0)))) | |

G4 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(B4,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(B4,{1,2},1)+0)+{0,1},MID(B4,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(B4,{1,2},1)+0,MID(B4,{1,2},1)+0)))) | |

H1 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(C1,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(C1,{1,2},1)+0)+{0,1},MID(C1,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(C1,{1,2},1)+0,MID(C1,{1,2},1)+0)))) | |

H2 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(C2,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(C2,{1,2},1)+0)+{0,1},MID(C2,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(C2,{1,2},1)+0,MID(C2,{1,2},1)+0)))) | |

H3 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(C3,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(C3,{1,2},1)+0)+{0,1},MID(C3,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(C3,{1,2},1)+0,MID(C3,{1,2},1)+0)))) | |

H4 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(C4,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(C4,{1,2},1)+0)+{0,1},MID(C4,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(C4,{1,2},1)+0,MID(C4,{1,2},1)+0)))) | |

I1 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(D1,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(D1,{1,2},1)+0)+{0,1},MID(D1,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(D1,{1,2},1)+0,MID(D1,{1,2},1)+0)))) | |

I2 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(D2,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(D2,{1,2},1)+0)+{0,1},MID(D2,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(D2,{1,2},1)+0,MID(D2,{1,2},1)+0)))) | |

I3 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(D3,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(D3,{1,2},1)+0)+{0,1},MID(D3,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(D3,{1,2},1)+0,MID(D3,{1,2},1)+0)))) | |

I4 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(D4,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(D4,{1,2},1)+0)+{0,1},MID(D4,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(D4,{1,2},1)+0,MID(D4,{1,2},1)+0)))) | |

J1 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(E1,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(E1,{1,2},1)+0)+{0,1},MID(E1,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(E1,{1,2},1)+0,MID(E1,{1,2},1)+0)))) | |

J2 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(E2,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(E2,{1,2},1)+0)+{0,1},MID(E2,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(E2,{1,2},1)+0,MID(E2,{1,2},1)+0)))) | |

J3 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(E3,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(E3,{1,2},1)+0)+{0,1},MID(E3,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(E3,{1,2},1)+0,MID(E3,{1,2},1)+0)))) | |

J4 | =IF(OR(AND(ISNUMBER(MATCH({0,9},MID(E4,{1,2},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID(E4,{1,2},1)+0)+{0,1},MID(E4,{1,2},1)+0,0)))),"C",INDEX({"S","D"},MAX(FREQUENCY(MID(E4,{1,2},1)+0,MID(E4,{1,2},1)+0)))) |