Hi Everyone,
I'm not good enough at Excel formulas, and even worse at Macros to be able to formulate anything that would do what I need...
I'll try to explain:
Need help with coming up with either a Macro or a formula which would count the skips between the picks of a certain number. For example, on the "Ball1 Stats" sheet, Starting with # 1 in B6, it has been picked 23 times (B7).
I would like to know how many skips are between each of the 23 times the #1 was picked in the Ball 1 column of the "Master" sheet.
On the "Master" sheet, in B column (Ball1) the list starts with date of 5/4/2015. Start counting down from B2, going down until the first instance of #1 is found for ball 1 is on 6/18/2015 (B15). From B2 to B14 is 13 skips, B15 is the #1. Then to represent the #1 put a #. Now Start counting again at B16. The next time #1 is picked is B18, 6/29/18 . B16 to B17 is 2 skips, then put a #.
On the "Master" sheet, I have listed about 290 Draws from the start 5/4/2015 to 2/19/2018 so far. The formula or macro should take into account the addition of future numbers to the master list. I would like to be able to apply the formula to each of the numbers, 1 to 60 for ball 1, ball 2, ball 3, ball 4 and ball 5. See attached test3 spreadsheet.
Need help with coming up with either a Macro or a formula which would count the skips between the picks of a certain number. For example, on the "Ball1 Stats" sheet, Starting with # 1 in B6, it has been picked 23 times (B7). I would like to know how many skips are between each of the 23 times the #1 was picked in the Ball 1 column of the "Master" sheet. On the "Master" sheet, in B column (Ball1) the list starts with date of 5/4/2015. Start counting down from B2, going down until the first instance of #1 is found for ball 1 is on 6/18/2015 (B15). From B2 to B14 is 13 skips, B15 is the #1. Then to represent the #1 put a #.
Now Start counting again at B16. The next time #1 is picked is B18, 6/29/18 . B16 to B17 is 2 skips, then put a #.
On the "Master" sheet, I have listed about 290 Draws from the start 5/4/2015 to 2/19/2018 so far. The formula or macro should take into account the addition of future numbers to the master list. I would like to be able to apply the formula to each of the numbers, 1 to 60 for ball 1, ball 2, ball 3, ball 4 and ball 5. See attached test3 spreadsheet.
Any help would be greatly appreciated. Thank you!!
Dave