Does the formula in col BQ return text or values? If returning text, I need a formula to return values. Thanks!
| GaMID Play 4 FOLLOWERS CODE 4 TEST.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| BJ | BK | BL | BM | BN | BO | BP | BQ | |||
| 19 | P1 | P2 | P3 | P4 | TYPE | CODE 4 | SDTC | BOX | ||
| 20 | 2 | 3 | 0 | 2 | 3+2 | 2302 | D | 0223 | ||
| 21 | 3 | 2 | 0 | 2 | 3+2 | 3202 | D | 0223 | ||
| 22 | 0 | 4 | 2 | 1 | 3+2 | 0421 | C | 0124 | ||
Pick4Rogue | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| BJ20:BJ22 | BJ20 | =IF($BI20="","",VALUE(MID(TEXT($BI20,"0000"),1,1))) |
| BK20:BK22 | BK20 | =IF($BI20="","",VALUE(MID(TEXT($BI20,"0000"),2,1))) |
| BL20:BL22 | BL20 | =IF($BI20="","",VALUE(MID(TEXT($BI20,"0000"),3,1))) |
| BM20:BM22 | BM20 | =IF($BI20="","",VALUE(MID(TEXT($BI20,"0000"),4,1))) |
| BN20:BN22 | BN20 | =LOOKUP(MMULT(LARGE(FREQUENCY(BJ20:BM20,J20:M20),{1,2}),{2;1}),{3,5,6,7,8,9,10},{"S","D","2+2","T","3+2","Q","P"}) |
| BO20:BO22 | BO20 | =BJ20&BK20&BL20&BM20 |
| BP20:BP22 | BP20 | =IF(OR(AND(ISNUMBER(MATCH({0,1,9},MID($BO20,{1,2,3,4},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID($BO20,{1,2,3,4},1)+0)+{0,1,2},MID($BO20,{1,2,3,4},1)+0,0)))),"C",INDEX({"S","D","T","Q"},MAX(FREQUENCY(MID($BO20,{1,2,3,4},1)+0,MID($BO20,{1,2,3,4},1)+0)))) |
| BQ20:BQ22 | BQ20 | =SMALL($BJ20:$BM20,1)&SMALL($BJ20:$BM20,2)&SMALL($BJ20:$BM20,3)&SMALL($BJ20:$BM20,4) |
| Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces. | ||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| BQ20:BQ3062 | Cell Value | ="C" | text | NO |
| BQ20:BQ3062 | Cell Value | ="S" | text | NO |
| BP20:BP10031 | Cell Value | ="C" | text | NO |
| BP20:BP10031 | Cell Value | ="S" | text | NO |
| BP19,BN20:BN10031 | Cell Value | ="C" | text | NO |
| BP19,BN20:BN10031 | Cell Value | ="S" | text | NO |
