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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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)
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Rick

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

M.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

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
Top