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

 

[Aladin Akyurek]

What do you mean by "period"?

 

[tx12345]

What do you mean by "period"?

Thanks for the reply.

Period refers to a group of 'rolling' rows on the way down the list. for example:

Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7


There are 4 sets of 4 in the above list of rows:

1-4, 2-5, 3-6, 4-7

tx

 

[Aladin Akyurek]

What do you mean by "period"?

Thanks for the reply.

Period refers to a group of 'rolling' rows on the way down the list. for example:

Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7


There are 4 sets of 4 in the above list of rows:

1-4, 2-5, 3-6, 4-7

tx

=IF(ROWS(A2:C5)=4,IF(ROW(C5)<=MATCH(9.99999999999999E+307,A:A),SUM((MMULT((A2:C5=$F$4)+0,{1;1;1})>0)+0),""),"")

Assuming that the number of columns is fixed...

F5, copied down:

=IF(ROW(INDEX(A2:$A$65536,$F$2))<=MATCH(9.99999999999999E+307,A:A),SUM((MMULT((A2:INDEX(C2:$C$65536,$F$2)=$F$4)+0,{1;1;1})>0)+0),"")

 

[tx12345]

F5, copied down:

=IF(ROWS(A2:C5)=4,IF(ROW(C5)<=MATCH(9.99999999999999E+307,A:A),SUM((MMULT((A2:C5=$F$4)+0,{1;1;1})>0)+0),""),"")

thanks again for the reply and good formula.

I think my question needs to be tuned up a bit. i was looking for a formula that will examine the entire range to find the maximum number of times (in our example) 120 appears in the sets of rows designated (4) on the way down.

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

this formula:

{=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))}

will show the maximum number of times (in this example) '120' appears, which is great. one formula, one cell. i was just wondering if there was a way to tweak the formula so that instead of:

examining the entire range and each 'rolling' set of rows, and then finding the maximum number of times the target number appears in the designated set of rows and columns

it would:

examine the entire range and each 'rolling' set of rows, and then find the maximum number of 'rows' the target number appears in the designated set of rows only.

in the first case, the current formula results in a 5, but in the next case the result is 2, as (in this example) looking at the entire range, and looking at each set of 4 rows, the maximum number of rows '120' appears in is only 2.

basically, the 120 is essentially 'duplicating', and i want to exclude duplicating instances of the target in the respective rows under examination.

does that make sense??

thanks so much for your insights

tx

 

[Aladin Akyurek]

F5, copied down:

=IF(ROWS(A2:C5)=4,IF(ROW(C5)<=MATCH(9.99999999999999E+307,A:A),SUM((MMULT((A2:C5=$F$4)+0,{1;1;1})>0)+0),""),"")

thanks again for the reply and good formula.

I think my question needs to be tuned up a bit. i was looking for a formula that will examine the entire range to find the maximum number of times (in our example) 120 appears in the sets of rows designated (4) on the way down.

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

this formula:

{=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))}

will show the maximum number of times (in this example) '120' appears, which is great. one formula, one cell. i was just wondering if there was a way to tweak the formula so that instead of:

examining the entire range and each 'rolling' set of rows, and then finding the maximum number of times the target number appears in the designated set of rows and columns

it would:

examine the entire range and each 'rolling' set of rows, and then find the maximum number of 'rows' the target number appears in the designated set of rows only.

in the first case, the current formula results in a 5, but in the next case the result is 2, as (in this example) looking at the entire range, and looking at each set of 4 rows, the maximum number of rows '120' appears in is only 2.

basically, the 120 is essentially 'duplicating', and i want to exclude duplicating instances of the target in the respective rows under examination.

does that make sense??

thanks so much for your insights

tx

Probably not understanding fully... What follows gives you total counts and counts with respect to a window size (period)...

aaCountOncePerRecordWithMMULT tx12345.xls
	A	B	C	D	E	F	G	H
1					Total Count:	4	3	4
2	312	412	212		Max	2	2	3
3	912	712	312		Window:	4	4	4
4	512	120	412		Target:	120	312	512
5	120	120	612			2	2	1
6	212	512	112			2	1	2
7	512	412	912			2	0	3
8	120	120	120			2	0	2
9	912	512	912			1	0	3
10	612	412	112			1	0	2
11	312	120	812			2	1	1
12
Sheet2

Total count...

F1:

=SUM((MMULT(($A$2:$C$11=F$4)+0,{1;1;1})>0)+0)

Max of rolling counts, which are generated (see below)...

F2:

=MAX(F5:INDEX(F5:F65536,MATCH(9.99999999999999E+307,F5:F65536)))

Generator for rolling counts...

F5:

=IF(ROW(INDEX($A2:$A$65536,$F$3))<=MATCH(9.99999999999999E+307,$A:$A),SUM((MMULT(($A2:INDEX($C2:$C$65536,F$3)=F$4)+0,{1;1;1})>0)+0),"")

 

[pgc01]

Hi again tx

I haven't been able yet to adpapt the formula to the new requirements.

However, If you want an UDF solution please try this code.

in G5

=RollingMax(A2:C11,G3,G4)

Hope this helps
PGC

EDIT: Corrected max value of lchar in the for loop, added 1

Option Base 1
Option Explicit

Function RollingMax(rR As Range, lTarget As Long, lPeriod As Long) As Long
Dim sCount As String, lRow As Long, lchar As Long, lMax As Long, lEval 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
    lEval = Evaluate(Mid(sCount, lchar * 2, lPeriod * 2 - 1))
    If lEval > lMax Then lMax = lEval
Next

RollingMax = lMax
End Function

Book1
	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
7	512	412	912
8	120	120	120
9	912	512	912
10	612	412	112
11	312	120	812
12
13
Sheet3

 

[tx12345]

Aladin: that is a well composed formula. thank you so much for providing your insight.

PGC: you sure know a lot about excel, vba, and numbers. that is an awesome little function. i expect you should become very rich (if not already) applying that knowledge to future forecasting, or arbitrage, or whatever else you can imagine. or just make big bucks helping companies perform more efficiently.

thanks again.

tx

 

[tx12345]

Hope this helps
PGC

Option Base 1
Option Explicit

Function RollingMax(rR As Range, lTarget As Long, lPeriod As Long) As Long
Dim sCount As String, lRow As Long, lchar As Long, lMax As Long, lEval 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
    lEval = Evaluate(Mid(sCount, lchar * 2, lPeriod * 2 - 1))
    If lEval > lMax Then lMax = lEval
Next

RollingMax = lMax
End Function

i have been tinkering with this code to get it to produce a minimum as well, and maybe even an average. not sure what to tweak. tried a few things, but my computer blew up

tx

 

[pgc01]

Hi again tx

I post the UDF for the MIN. As you can see it's the same logic. In the MAX you start from 0 and go up. In the MIN you start from lPeriod and go down.

Option Base 1
Option Explicit

Function RollingMin(rR As Range, lTarget As Long, lPeriod As Long) As Long
Dim sCount As String, lRow As Long, lchar As Long, lMin As Long, lEval As Long

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

lMin = lPeriod
For lchar = 1 To rR.Rows.Count - lPeriod + 1
    lEval = Evaluate(Mid(sCount, lchar * 2, lPeriod * 2 - 1))
    If lEval < lMin Then lMin = lEval
Next

RollingMin = lMin
End Function

Hope it's clear
PGC

EDIT: Corrected max value of lchar in the for loop, added 1