How do I get the formula in Col's F:J return the value for leading zeroes?
Excel 2007
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 |
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)))) |