summing the highest digits in a string of numbers

[dwrowe001]

Hi,
despite what the title says, it isn't as easy as just that...
What I'm doing is tracking the number of times a number is skipped, incrementing 1 each skip, then when the number is drawn a "0" is entered into the string, and the process starts again. so it looks something like this:
12340123456780123456789101112130120.... each zero is when the number is drawn.. then the next drawn if the number isn't picked a 1 is entered, adding 1 for each time it is skipped. I have the numbers going down from B1 down to B262.

I have this formula copied down from B16 to B300:
IF('Number Counts'!A3="","",IF(COUNTIF('Number Counts'!B3,1),0,B13+1))

I would like to stream line the process, instead of having the long string of numbers from B16 down to B262, I would like to have the current skip count appearing in 1 Cell.

Also, I would like to add the last digit before the number hits.. In the below string string example:
12340123456780123456789101112130120, I would like to add the numbers 4, 8, 13, 2... there would be more numbers of course, this is shortened for simplicity.

Hope these make sense...
thanks
Dave

 

[Peter_SSs]

I'm not exactly sure
- what you have
- what you want
- where you want it

Any chance you could show a few different examples of your data, layout and expected results and explain again in relation to that?
My signature block below has a link for suggestions of how you can do that.

 

[Peter_SSs]

I don't see a feasible way to do this with standard worksheet formulas, but you could try the user-defined functions. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formulas as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function CountSkips(s As String) As Long
  Dim stmp As String
  Dim j As Long

  j = 1
  Do
    If s Like stmp & j & "*" Then
      stmp = stmp & j
      j = j + 1
    Else
      CountSkips = CountSkips + 1
      stmp = stmp & 0
      j = 1
    End If
 Loop Until Len(stmp) >= Len(s)
End Function


Function SumHigh(s As String) As Long
  Dim stmp As String
  Dim j As Long

  j = 1
  Do
    If s Like stmp & j & "*" Then
      stmp = stmp & j
      j = j + 1
    Else
      SumHigh = SumHigh + j - 1
      stmp = stmp & 0
      j = 1
    End If
 Loop Until Len(stmp) >= Len(s)
End Function

Excel 2016 (Windows) 32 bit
	A	B	C
1		Skips	Sum
2	12340123456780123456789101112130120	4	27
3	101230123012340120	5	13
4	12340123	1	4
Sheet1

Cell Formulas
Range	Formula
B2	=CountSkips(A2)
C2	=SumHigh(A2)

Since you only gave one example, I'm not sure if all strings will end with a zero or whether examples like row 4 above are possible?
If that is possible, what results do you want for each of Skips and Sum?

 

[dwrowe001]

Peter,
I apologize for not being more clear and specific of what I need help with. I’ll try to explain better..
I am tracking lotto picks. Lotto has 5 balls, with a number pool of 60. This specific issue I need help with is dealing with how many times a given number is skipped.
I have all picks listed on tab named Working 1:[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date
[/TD]
[TD]ball 1
[/TD]
[TD]ball 2
[/TD]
[TD]ball 3
[/TD]
[TD]ball 4
[/TD]
[TD]ball 5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]5/4/2015
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]7
[/TD]
[TD]9
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]5/7/2015
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[TD]11
[/TD]
[TD]22
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]5/11/2015
[/TD]
[TD]5
[/TD]
[TD]8
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]5/13/2015
[/TD]
[TD]13
[/TD]
[TD]19
[/TD]
[TD]33
[/TD]
[TD]45
[/TD]
[TD]59
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5/17/2015
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]12
[/TD]
[TD]33
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In my Original post I was tracking the number 1.. in the above example, 1 is drawn 2 times for ball 1. my list starts tracking the lotto on 5/4, with the #3 , then on 5/7 #1 is picked for ball 1, so there would be 1 skip before ball 1 is picked on 5/7. then #1 isn't picked again till 5/17, there are 2 skips between 5/7 and 5/17. I am trying to come up with a formula that would add the number of skips between draws.. in my original post I said I was using this formula:
IF('Number Counts'!A3="","",IF(COUNTIF('Number Counts'!B3,1),0,B13+1)) to get the below results:
12340123456780123456789101112130120. The zeros represent when the number 1 is drawn. the numbers between the zeros are the number of skips. I would like to try to add the last digits before the zeros, which represent the number of skips before the number is drawn.. so that would be 4,8,13,2 = 27. I'm adding new picks weekly to the list on Working 1.

Thank you
Dave

 

[Peter_SSs]

Hmm, that certainly does seem a bit different to what was posted to start with.

I have all picks listed on tab named Working 1:[TABLE="class: grid, width: 200"]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date
[/TD]
[TD]ball 1
[/TD]
[TD]ball 2
[/TD]
[TD]ball 3
[/TD]
[TD]ball 4
[/TD]
[TD]ball 5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]5/4/2015
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]7
[/TD]
[TD]9
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]5/7/2015
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[TD]11
[/TD]
[TD]22
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]5/11/2015
[/TD]
[TD]5
[/TD]
[TD]8
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]5/13/2015
[/TD]
[TD]13
[/TD]
[TD]19
[/TD]
[TD]33
[/TD]
[TD]45
[/TD]
[TD]59
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5/17/2015
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]12
[/TD]
[TD]33
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
.. number 1.. in the above example, 1 is drawn 2 times for ball 1. my list starts tracking the lotto on 5/4, with the #3 , then on 5/7 #1 is picked for ball 1, so there would be 1 skip before ball 1 is picked on 5/7. then #1 isn't picked again till 5/17, there are 2 skips between 5/7 and 5/17. I am trying to come up with a formula that would add the number of skips between draws..

So presumably the result for the above simple example is 3 (1+2)??

Suppose that red "1" had been anything else, would the expected result be 1 or would it be 4, or something else?
If the answer to that is 4, then try this formula. Note that I have set up the 'ball 1' to replicate what I think matches your earlier "12340123456780..." example

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	ball 1	ball 2	ball 3	ball 4	ball 5		No.	Ball	Skips
2	14	1	30	18	25		1	1	27
3	45	52	11	49	18
4	19	58	52	24	51
5	5	42	22	26	52
6	1	51	26	16	20
7	40	28	27	38	37
8	17	18	27	7	15
9	5	17	14	54	48
10	38	33	47	48	21
11	41	36	51	10	47
12	53	57	34	60	38
13	4	28	5	32	16
14	28	59	42	9	21
15	1	49	49	28	58
16	30	20	56	13	37
17	22	15	24	50	14
18	17	8	58	32	44
19	45	30	10	59	29
20	35	32	30	54	47
21	44	49	41	51	25
22	53	57	32	45	60
23	14	9	1	11	40
24	12	54	51	17	29
25	30	57	12	44	22
26	24	37	25	51	45
27	34	2	21	48	26
28	43	41	29	27	21
29	1	35	47	60	31
30	54	1	46	23	24
31	46	23	21	23	13
32	1	42	52	11	46
33
Working 1

An alternative formula that should do the same job would be:

=COUNT(INDEX(B2:F40,0,I2))-COUNTIF(INDEX(B2:F40,0,I2),H2)