Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace the Find Function with a formula | Excel Worksheet Functions | |||
Formula to replace a Pivot table function | Excel Discussion (Misc queries) | |||
How to replace a function with its resulting reference in a formula? | Excel Worksheet Functions | |||
How to replace a function with its result or resulting reference in a formula? | Excel Worksheet Functions | |||
IF function with Array Formula | Excel Programming |