The formulas in E2-G5 count the number of times the values hit with each value. Formulas in L2-M5 count the number of skips. The formulas may not be working because of the format of B2-D2 which comes from the A2 value. Not an Excel guru, thans for all responses.
| CASH 3 GA MID REPUBLICAN-DEMOCRAT NUMBERS .xlsm | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Column1 | ST A | ST B | ST C | ST B2 | ST C2 | ST D2 | PKG | MED | MAX | AVG | SKIPS | 2 | ||
| 2 | 11112110000111001101 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | #NUM! | 0 | #REF! | ||||
| 3 | 10110101101122110010 | 1 | 0 | 1 | 1 | 0 | #NUM! | 0 | #REF! | ||||||
| 4 | 11111201012201110210 | 1 | 1 | 1 | 2 | 0 | #NUM! | 0 | #REF! | ||||||
| 5 | 11202000110012202112 | 1 | 1 | 2 | 3 | 0 | #NUM! | 0 | #REF! | ||||||
Sheet3 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B5 | B2 | =((IF($A2="","",MID(TEXT($A2,"00000000000000000000"),1,1)+0))*1)*1 |
| C2:C5 | C2 | =((IF($A2="","",MID(TEXT($A2,"00000000000000000000"),2,1)+0))*1)*1 |
| D2:D5 | D2 | =((IF($A2="","",MID(TEXT($A2,"00000000000000000000"),3,1)+0))*1)*1 |
| H2 | H2 | =SUMPRODUCT(--($B$2:$B$5=$E$2),--($C$2:$C$5)=$F$2,--($D$2:$D$5=$G2)) |
| I2:I5 | I2 | =MEDIAN(L2:X2) |
| J2:J5 | J2 | =MAX(L2:X2) |
| K2:K5 | K2 | =COUNT('[GA 3 MID CN-NCN STR DIGIT MATRIX.xlsx]SKIPSUMS'!$D:$D)/#REF! |
| L2:M5 | L2 | =CHOOSE(IF(N($Y2),IF(COLUMNS(L2:$AD2)=1, 1, IF(COLUMNS(L2:$AD2)<=$Y2,2,3)),3),SMALL(IF($B$2:$B$5405&"@"&$C$2:$C$5405=$V$2&"@"&$X2,ROW($B$2:$B$5405)-ROW($B$2)),1),SUM(SMALL(IF($B$2:$B$5405&"@"&$C$2:$C$5405=$V$2&"@"&$X2,ROW($B$2)),{1,0}+COLUMNS(L2:$AD2)-1)*{1,-1}),"") |
| H3:H5 | H3 | =SUMPRODUCT(--($B$2:$B$1100=$E$2),--($C$2:$C$1100)=$F$2,--($D$2:$D$1100=$G3)) |
| Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces. | ||
