Replace function with array formula
 

Hi,
Question: What is the array formula equivalent (not a function or macro)
that will return an array with the same results as the function below?

Given a range of values (text or numbers):
13
13
14
13
14
13
13
14
13
13
14
13
13
14

The following function will return the count of cells between each 14:

Returns : ,3,2,3,3,3

Function valuesbetween(MyRange, MyValue)
Count = 0
For Each zzz In MyRange
Count = Count + 1
If zzz = MyValue Then
mytotal = mytotal & "," & Count
Count = 0
Else
End If
Next zzz
valuesbetween = mytotal
End Function

Replace function with array formula
 

If your data is in col A, then in C2, say (C1 being blank),
ctrl/shift/enter:
=MATCH(TRUE,OFFSET($A$1:$A$14,SUM($C$1:C1),0)=14,0 )
and fill down.


"Don" wrote in message
...
Hi,
Question: What is the array formula equivalent (not a function or macro)
that will return an array with the same results as the function below?

Given a range of values (text or numbers):
13
13
14
13
14
13
13
14
13
13
14
13
13
14

The following function will return the count of cells between each 14:

Returns : ,3,2,3,3,3

Function valuesbetween(MyRange, MyValue)
Count = 0
For Each zzz In MyRange
Count = Count + 1
If zzz = MyValue Then
mytotal = mytotal & "," & Count
Count = 0
Else
End If
Next zzz
valuesbetween = mytotal
End Function

Replace function with array formula
 

Thanks Bob - It works great.


"Bob Umlas" wrote in message
...
If your data is in col A, then in C2, say (C1 being blank),
ctrl/shift/enter:
=MATCH(TRUE,OFFSET($A$1:$A$14,SUM($C$1:C1),0)=14,0 )
and fill down.


"Don" wrote in message
...
Hi,
Question: What is the array formula equivalent (not a function or macro)
that will return an array with the same results as the function below?

Given a range of values (text or numbers):
13
13
14
13
14
13
13
14
13
13
14
13
13
14

The following function will return the count of cells between each 14:

Returns : ,3,2,3,3,3

Function valuesbetween(MyRange, MyValue)
Count = 0
For Each zzz In MyRange
Count = Count + 1
If zzz = MyValue Then
mytotal = mytotal & "," & Count
Count = 0
Else
End If
Next zzz
valuesbetween = mytotal
End Function