Consecutive amounts greater than 2500

[rodgo]

I have a row of numbers. I would like to get the number of consecutive times the number is above 2500. For example, A1 through Z1 contain numbers. If C1 through E1 contain numbers above 2500 I want the number returned to be 3. Now, if there are several times that this happens between A1 and Z1, then i want the 'longest streak'.

Please tell me you all understand!?!?!

 

[acw]

Hi

A2: =IF(A1>2500,1,0)
B2: =IF(B1>2500,A2+1,0)
copy from B2 to Z2
A3: =MAX(A2:Z2)

Another way would be to build your own function. Something like

Function MyFunc(ra As Range, tester)
  final = 0
  interim = 0
  For Each ce In ra
    If ce > tester Then
      interim = interim + 1
    Else
      If interim > final Then
        final = interim
      End If
      interim = 0
    End If
  Next ce
  MyFunc = final
End Function

With your data in the range A1:Z1 use the formula in the form
=myfunc(A1:Z1,2500)

HTH

Tony

 

[Domenic]

Here's another way...

=MAX(FREQUENCY(IF(A1:Z1>2500,COLUMN(A1:Z1)),IF(A1:Z1<=2500,COLUMN(A1:Z1))))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!