Return value for every 5 positive numbers in a column !

marcialfa

New Member
Joined
Jan 11, 2017
Messages
12
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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...
Code:
[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)
 
Upvote 0
Rick

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

M.
 
Last edited:
Upvote 0
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).
 
Upvote 0
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 !
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top