Return value for every 5 positive numbers in a column !

[marcialfa]

Hello guys,

I am using this formula =SUMPRODUCT(--(FREQUENCY(IF(D3:D38>=5,ROW(D3:D38)),IF(D3:D38<=4,ROW(D3:D38)))=5)) which is an array to identify every 5 consecutive numbers above 5.

The only problem is if there are more then 5 consecutive numbers and not exact 5 then it wont return anything which is not helpful the formula works for exact 5 consecutive numbers.

-2
-1
3
8
7
6
5
8
9
4
3
2
1

so, using this formula it will return zero as I have more then 5 consecutive numbers above 5, when it should return 1,and in the case of having 10 consecutive numbers higher then 5 it should display 2. For every five consecutive numbers above 5.

Thank you for your support.

 

[Marcelo Branco]

Maybe this array formula

=SUM(INT(FREQUENCY(IF(D3:D38>=5,ROW(D3:D38)),IF(D3:D38<=4,ROW(D3:D38)))/5))
confirmed with Ctrl+Shift+Enter

Hope this helps

M.

 

[Rick Rothstein]

I know the OP wanted a formula solution, but since I like one-liners and this UDF (user defined function) is a one-liner, I thought I would post it...

[table="width: 500"]
[tr]
	[td]Function Consec(Rng As Range, Consecutives As Long)
  Consec = UBound(Split(Join(Application.Transpose(Evaluate("IF(" & Rng.Address & "<0,0,1)")), ""), String(Consecutives, "1")))
End Function[/td]
[/tr]
[/table]

The function takes two argument, the vertical range to process and the number of consecutive positive values to count. So, for the example in Message #1, that UDF formula would be...

=Consec(A1:A13,5)

 

[Marcelo Branco]

Rick

I think the OP wants to count sequences of 5 numbers consecutives that are greater than or equal 5

M.

 

[Rick Rothstein]

Rick

I think the OP wants to count sequences of 5 numbers consecutives that are greater than or equal 5

The OP's question was not stated as clearly as one would like, but piecing together what he wrote coupled with the title of this thread, I assumed the OP wanted to count groups of 5 consecutive (meaning "next to each other", not numerically sequential) numbers that were all greater than or (I assumed) equal to zero (hence, positive).

 

[marcialfa]

Hello again, I have been really busy at work

Thank you so far for your help... please check the original file https://www.dropbox.com/s/tiqzrquea2eh8ab/Whist Score board - maybe.xlsm?dl=0
and I have coloured the cell red where I want the formula to be.

So the file is a card game score and if a player scores 5 consecutive times (everything above 5) another 5 points will be added as a bonus to his score; my current formula is not working because if there is more then 5 consecutive times will display 0 when it should display 1 for every 5 consecutive numbers over 5.

Please check the file and let me know what I have done wrong and how it can be done.

Thank you again !