how to count streak in range ascending

[nadavrock]

i have a column with numbers 0-10. i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0. from the top. how do i do this?

 

[alansidman]

Assumes your data is in column A. Here is a VBA solution

Option Explicit


Sub CountColA()
    Dim x As Long
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    x = 0
    For i = 1 To lr
        If Range("A" & i) > 0 Then
            x = x + 1
        End If
    Next i
    MsgBox ("Total Number above zero is " & x)
End Sub

 

[MARK858]

i have a column with numbers 0-10. i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0. from the top. how do i do this?

with the below what answer would you want returned?

Excel 2010
	A
1	7
2	2
3	0
4	7
5	0
6	2
7	7
8	10
9	6
10	10
11	0
12	0
13	1
14	6
15	7
16	0
Sheet4

and what answer would you want returned if A16 was 4 rather than zero?

 

[Rick Rothstein]

Assuming your numbers are in Column A starting at Row 1, this UDF (user defined function) will return the length of the longest contiguous run of numbers greater than zero...

[table="width: 500"]
[tr]
	[td]Function Streak()
  Dim x As Long, Arr As Variant
  Arr = Split(Application.Trim(Join(Evaluate("TRANSPOSE(IF(A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & ">0,1,"" ""))"), "")))
  For x = 0 To UBound(Arr)
    If Len(Arr(x)) > Streak Then Streak = Len(Arr(x))
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,

=Streak()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[MARK858]

Rick, hopefully you are right that the OP wants the longest streak as I am not sure exactly what they mean by

i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0

Which is why I made post number 3.

 

[Twollaston]

i have a column with numbers 0-10. i want to display in a desginated cell. the current streak. as in currently how many cells in a row are greater than 0. from the top. how do i do this?

Is this what you are looking for?

Data	Streak
5	1
3	2
2	3
4	4
54	5
0	0
5	1
5	2
0	0
0	0
0	0
5	1
0	0
25	1
67	2
48	3
0	0

<colgroup><col width="68" span="2" style="width:51pt"> </colgroup><tbody>
</tbody>

Try this formula
=IFERROR(IF(A2<>0,B1+1,0),1)
NOTE: dont post this formula into row 1, start it at row 2 or else you will have manually populate the first cell with a number 1 and then fill in the formula below it

 

[Rick Rothstein]

Rick, hopefully you are right that the OP wants the longest streak as I am not sure exactly what they mean by

It was a guess on my part which is why I specified to the OP what the code will return.

 

[nadavrock]

thanks for the replies. my intention was not the longest streak but the latest streak.
with data as listed below. the latest streak read from the top would be 3 in a row greater than 0. that is what i want to display in a seperate cell. how do i do this?

a1 3
a2 4
a3 8

a4 0
a5 1
a6 1
a7 1
a8 1

 

[Rick Rothstein]

thanks for the replies. my intention was not the longest streak but the latest streak.
with data as listed below. the latest streak read from the top would be 3 in a row greater than 0. that is what i want to display in a seperate cell. how do i do this?

a1 3
a2 4
a3 8

a4 0
a5 1
a6 1
a7 1
a8 1

Give this UDF a try (install instructions in Message #4, note function name change though)...

[table="width: 500"]
[tr]
	[td]Function LatestStreak()
  LatestStreak = Len(Split(Application.Trim(Join(Evaluate("TRANSPOSE(IF(A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & ">0,1,"" ""))"), "")))(0))
End Function[/td]
[/tr]
[/table]

 

[nadavrock]

works like a charm. thank you