Count consecutive entries before a number occurs

[Nintapper]

Hi there. New Year greetings from Spain.
Can you help me with this? I have a column of numbers *normally 500 entries and I would like to be able to calculate the highest number of consecutive entries before a number over a certain amount appears. For example the value is -5.50. So what is the highest consecutive entries before -5.51 (or larger -20.10) appears in the data set.
My data in A looks something like this:
-7.5
0.2
0.0
-0.7
3.6
-0.2
8.6
0.3
0.0
-1.1
5.0
-5.1
-3.2
-6.0
11.1
18.1
-2.70
-5.52


The answer should be 12 (entries) before -6.0 appears in the data.
Many thanks.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02Jan48
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value > -5.51 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
    oMax = Application.Max(oMax, Dn.Count)
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox "Max consequtive Range Count >-5.51= " & oMax
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Nintapper]

Hi Mick,
Thanks but I can't get this to work and I'm not even sure how or where to enter the code. This is probably a stage or a few stages further than I have taken Excel before.

 

[MickG]

Try this instruction:-
To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
Msgbox should now Show.



NB:- To show the results in sheet cell "B1", change the line below as shown:-

From This:-

MsgBox "Max consequtive Range Count >-5.51= " & oMax

To This :-

Range("B1").value = oMax

Regrds Mick

 

[Nintapper]

Hi Mick
That's awesome. They both worked a treat. Thank you so much.

 

[Nintapper]

Can this also be done using a function, Frequency and creating an array?

 

[Eric W]

Is this what you're looking for?

	A	B
1	-7.5	12
2	0.2
3	0
4	-0.7
5	3.6
6	-0.2
7	8.6
8	0.3
9	0
10	-1.1
11	5
12	-5.1
13	-3.2
14	-6
15	11.1
16	18.1
17	-2.7
18	-5.52

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula B1 {=MAX(FREQUENCY(IF(A1:A18>-5.5,ROW(A1:A18)),IF(A1:A18<=-5.5,ROW(A1:A18))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The formula will find the longest run in the range where all the values are > -5.5