Edit Formula To Return Leading Zeroes

[CARBOB]

How do I get the formula in Col's F:J return the value for leading zeroes?

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))))

 

[CARBOB]

Not sure which of the various formula will be most efficient with the size of your data set, but a couple more for you to try.

=LOOKUP(ABS(INT(A1/10)-MOD(A1,10)),{0,1,2,9},{"D","C","S","C"})

=INDEX({"D","C","S","C"},MATCH(ABS(INT(A1/10)-MOD(A1,10)),{0,1,2,9},1))


I've only tested for values up to 99, not sure how results would compare with anything above that if there is possibility of such values in your data.

No digits above 99, thanks again!!