Frank
Member
This is my attempt at getting instantaneous skip readout for 649 without bonus. Assumes the data starts with draw 1 at the top down to the most recent draw at the bottom and all draws are numbered.
https://www.mediafire.com/?oekkcqt503ajta2
As I mentioned earlier, I don't think the original LOOKUP formula format can scan multiple columns, so if you are looking for the latest ocurrence of a ball which might appear in any of 6 columns, you need 6 versions of the formula checking six columns. I dispensed with the ABS part of the formula, it was superfluous in this case.
It is not necessary to instantly calculate a skip because that depends on which of the six columns holds the most recent ocurrence and its draw number. Initally that part of the calculation is left out of the formula, we just need to find the most recent draw number for each number per column.
So for 49 balls drawn in six columns thats 49 x 6 calculations followed by 49 more calculations to assess the most recent draw number per ball, hence the skip.
I created a grid of calculating cells along seven rows to accomplish this. However, how does one know where to start looking up from when users can add new draws to the bottom of the list? I had to use the OFFSET function to find the row number of the last draw in the table, helped by a draw counter in a named cell - called Maxdraw. So the original LOOKUP idea was modified to use the column headers (in particular Cell B12) as a datum to work from to find the last result.
I ended up with a 49 x 6 row counting grid containing the draw numbers of each number's last ocurrence. However not every number appears in every column, and where they don't, I was getting N/A in those cells. So I had to check each cell for the N/A message and replace each with a "" (blank), making the formula even more complicated.
By finding the Max of the six rows per number, I got the last appearance draw number. Then the final 49 formulas subtracted these from the current draw number to get the skip.
Assumptions:- a 649 lottery bonus ball not included.
most recent draw number at the bottom
draws numbered ascending downwards.
https://www.mediafire.com/?oekkcqt503ajta2
As I mentioned earlier, I don't think the original LOOKUP formula format can scan multiple columns, so if you are looking for the latest ocurrence of a ball which might appear in any of 6 columns, you need 6 versions of the formula checking six columns. I dispensed with the ABS part of the formula, it was superfluous in this case.
It is not necessary to instantly calculate a skip because that depends on which of the six columns holds the most recent ocurrence and its draw number. Initally that part of the calculation is left out of the formula, we just need to find the most recent draw number for each number per column.
So for 49 balls drawn in six columns thats 49 x 6 calculations followed by 49 more calculations to assess the most recent draw number per ball, hence the skip.
I created a grid of calculating cells along seven rows to accomplish this. However, how does one know where to start looking up from when users can add new draws to the bottom of the list? I had to use the OFFSET function to find the row number of the last draw in the table, helped by a draw counter in a named cell - called Maxdraw. So the original LOOKUP idea was modified to use the column headers (in particular Cell B12) as a datum to work from to find the last result.
I ended up with a 49 x 6 row counting grid containing the draw numbers of each number's last ocurrence. However not every number appears in every column, and where they don't, I was getting N/A in those cells. So I had to check each cell for the N/A message and replace each with a "" (blank), making the formula even more complicated.
By finding the Max of the six rows per number, I got the last appearance draw number. Then the final 49 formulas subtracted these from the current draw number to get the skip.
Assumptions:- a 649 lottery bonus ball not included.
most recent draw number at the bottom
draws numbered ascending downwards.