• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help counting skips between instances of numbers

D

dwrowe001

Member
Hi all,
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 #1 was picked in 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. I am wanting to count skips between picks of the number 1.. So the first time the number 1 is picked 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 Zero. 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 0. Do this all the way down to Say B500 to cover future picks. I would like to do this for each of the numbers, 1 to 60 for ball 1, ball 2, ball 3, ball 4 and ball 5.

upload_2018-3-2_16-44-21.png

Thank you in advance, and as always your help is greatly appreciated.
Dave
 

Attachments

dwrowe001 said:
Hi all,
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 #1 was picked in 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. I am wanting to count skips between picks of the number 1.. So the first time the number 1 is picked 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 Zero. 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 0. Do this all the way down to Say B500 to cover future picks. I would like to do this for each of the numbers, 1 to 60 for ball 1, ball 2, ball 3, ball 4 and ball 5.

View attachment 50376

Thank you in advance, and as always your help is greatly appreciated.
Dave
Click to expand...

Hi Narayan,
That's great, almost got it.. there's a couple things that need tweeking If I may....

Instead of Zero's representing the number picked, can it be a "#"? So for example: say for Ball 1 No. 1, it would look like this:
upload_2018-3-3_17-52-32.png

Note that B26 and B27 are both #.. this indicates that No.1 was drawn consecutively. D13 indicates that No3. was drawn first on 5/4/2015 for Ball 1.

At the bottom of the Ball1 Stats sheet,
upload_2018-3-3_17-58-22.png
Notice for No.1 the last number is 1, this means there has been 1 skip after the last time it was drawn.. if the number 1 is skipped again on the next draw, the number would increment by one to 2.. this would be how it would be for all 60 numbers.

I noticed that further down the Ball1 Stats sheet, there are numbers? is there a way get rid of them?

Thanks for all your help.

Dave
 
Hi ,

See if this is OK.

Regarding the removal of the formulae in rows 97 and beyond , I am afraid I cannot develop a formula which does not make use of these helper cells.

The formulae in the final output area of B13:BI90 are based on the formulae in the range B97:BI500.

Narayan
 

Attachments

Thanks for your help Narayan, I do appreciate it.
Full disclosure, not thinking that it was taboo, I posted this same question on another site for assistance. Member on other site called me out about it.. Apologies for this. just want to let you know..
I will check test what you sent back. Again, thanks for your help.
 
You must log in or register to reply here.
Back
Top