Reading the thread and learning about Reference Boxes was a refreshing change from chasing formula histories to predict numbers.
I applied the system to the CA Daily 4.
The setup in excel to create the Box is fairly simple but takes a little time to do.
In cell [B18]
First import or create the Game Data for you Game. Setup as follows:
Cell [B18] Game Number; [C18] 1st N1; [D18] 2nd N2; [E18] 3rd N3; [F18] 4th N4
Use the negative value of the game Number so that the Vertical Lookup VLOOKUP function can find selected games in the formulas to be created.
In Cell [I1] enter any number (20). Highlight this cell. This cell will become the trigger to change the number of games back you want to go.
In Cell [I3] enter any number (30). Highlight this cell. This cell will become the trigger to Set the Number of Games you want to go back to set the Base Game for the Box. The number entered is the number of games to go back for the Base Game.
In cells [I5:I14] enter 0 thru 9 in increasing order.
In cells [J5] enter this formula: =COUNTIF(INDIRECT(“C”&$I$2+ROW($B$17)):INDIRECT(“C”&$I$1+$I$1+ROW($B$17)),I5) Copy this formula in cells [J6:J14]
In cells [M5] enter this formula: =COUNTIF(INDIRECT(“D”&$I$2+ROW($B$17)):INDIRECT(“D”&$I$1+$I$1+ROW($B$17)),I5) Copy this formula in cells [M6:M14]
In cells [R5] enter this formula: ECOUNTIF(INDIRECT(“E”&$I$2+ROW($B$17)):INDIRECT(“C”&$I$1+$I$1+ROW($B$17)),I5) Copy this formula in cells [R6:R14]
In cells [V5] enter this formula: =COUNTIF(INDIRECT(“F”&$I$2+ROW($B$17)):INDIRECT(“F”&$I$1+$I$1+ROW($B$17)),I5) Copy this formula in cells [V6:V14]
In Cell [C4] type Game, In cell [F4] type Games
In cell [D4] enter this formula: =INDIRECT(“B”&ROW($B$18)+I2)*-1
In cell [E4] enter this formula: =$I$1
In cell [C5] enter this formula: =I5 Copy this formula in cells [C6:C8]
In cell [D5} enter this formula: =M5 Copy this formula in cells [D6:D8]
In cell [E5} enter this formula: =Q5 Copy this formula in cells [E6:E8]
In cell [F5} enter this formula: =U5 Copy this formula in cells [F6:F8]
To create a Reference Box enter the Number of games and Games Back in the trigger cells. [I1] Games included [I2] Games back for Base Game.
Sort each of the column pairs:
[I5:J14] Sort in descending order in column J and by increasing order in column I
[M5:N14] Sort in descending order in column N and by increasing order in column M
[Q:R14] Sort in descending order in column R and by increasing order in column Q
[U5:V14] Sort in descending order in column V and by increasing order in column U
If you do the time you will be able to calculate as many Reference Boxes as you want.
By the way. I only ran one test with a Game Base 30 back and a Block of 20 games for CA Daily 4. The resulting Box had 192 possible combinations to play (maintaining positions in the columns). The hits are as follows: 2667, 2688, 2670, 2671, 2676, 2680, 2684, 2686, 2689, and 2690 all hit Boxes. 10 out of 30 Games.
Looks like it might be worth the effort.
From this basic worksheet you can create workbooks for any Pick 4 game. You can modify it to a Pic 3 by not using the N4 column and N4 Count column or by erasing them. Do not delete any columns. It might wreck things.
From this Basic Worksheet you can go further and set up tables that instantly show how any Reference Box has performed over the next games after the Base Game.