PDA

View Full Version : counts ships between instances of picked number



dwrowe001
03-03-2018, 07:10 PM
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

p45cal
03-04-2018, 12:12 PM
Grrr.
Cross posted https://chandoo.org/forum/threads/help-counting-skips-between-instances-of-numbers.37596/ without links.
Wasted silly amounts of time on this.

dwrowe001, please read http://www.excelguru.ca/content.php?184

dwrowe001
03-04-2018, 02:59 PM
I apologize. I was unaware that helping someone was a waste of time.. I'm sorry for placing my question on different sites trying to find the best answer to my question. I honestly didn't realize I had to post links if I did this. Please disregard my original post.

p45cal
03-05-2018, 03:28 AM
I was unaware that helping someone was a waste of time.. How trite.

You text both your friends because you want to get to the airport.
You go with the first one that turns up.
When the the second one complains you've wasted their time, you explain to them that you were unaware that helping someone was a waste of time.
You're surprised to find you now have only one friend.

dwrowe001
03-05-2018, 07:07 AM
again, I'm sorry for cross posting my question.. I read the post on the rules of cross posting. I was wrong. I admitted it in my first post and even admitted what I did to the person helping me on the other site. I won't do it again and certainly won't do it without providing links.

P.S. Not even I would text two friends to come pick me up at the airport, then go with the first who showed up. That's just wrong.