counting 'unique' instances of 'x' in a rolling range

[tx12345]

hi

this is a follow up question to a previous thread.

with this formula (thanks to PGC01):

{=MAX(COUNTIF(OFFSET($A$2:$C$11,-1+ROW(INDIRECT("1:"&ROWS($A$2:$C$11)-F4+1)),0,F4,COLUMNS($A$2:$C$11)),F3))}

we get this result using the data set shown below:

312	412	212			
912	712	312		 Target: 120
512	120	412		 'Rolling' set of rows: 4
120	120	612 <-|	       Max Result: 5
212	512	112 <-|			
512	412	912 <-|		
120	120	120 <-|	
912	512	912			
612	412	112			
312	120	812

As you can see, in the section of 4 rows highlighted, 120 indeed appears 5 times in total. But it only appears in two of the four rows, which would give another result of '2' instead of '5'.

i forgot to follow up on the other thread how to adjust the excellent formula shown above to not only show the aggregate total of all instances in each 'rolling' set, which it does very well, but also to count only the number of rows the target number appears in as well.




tx

 

[pgc01]

Hi again tx

As you may have noticed there was one cycle missing in the second For.

I corrected it in both UDFs

Correct:

For lchar = 1 To rR.Rows.Count - lPeriod + 1

Sorry
PGC

 

[pgc01]

Hi tx

Here goes the Average of the running total, the last one you requested.

Cheers
PGC

Function RollingAvg(rR As Range, lTarget As Long, lPeriod As Long) As Double
Dim sCount As String, lRow As Long, lchar As Long, dAvg As Double, lTot As Long

For lRow = 1 To rR.Rows.Count
    sCount = sCount & "+" & Evaluate("=--(countif(" & rR.Rows(lRow).Address & "," & lTarget & ")>0)")
Next

For lchar = 1 To rR.Rows.Count - lPeriod + 1
    lTot = lTot + Evaluate(Mid(sCount, lchar * 2, lPeriod * 2 - 1))
Next

RollingAvg = lTot / (rR.Rows.Count - lPeriod + 1)
End Function

rollingmax.xls
	A	B	C	D	E	F	G	H
1
2	312	412	212
3	912	712	312			Target:	120
4	512	120	412			Period:	5
5	120	120	612			MaxResult:	3
6	212	512	112			MinResult	1
7	512	412	912			Avg	2
8	120	120	120
9	912	512	912
10	612	412	112
11	312	120	812
12
13
Sheet3

 

[tx12345]

thanks so much for the effective functions. i really have to learn how to do that. any resources to learn how to write functions in VBA?

thnaks

tx