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

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
hi

this is a follow up question to a previous thread.

with this formula (thanks to PGC01):

Code:
{=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:

Code:
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
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:

Code:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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:

Code:
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

Board Regular
Joined
Aug 28, 2006
Messages
165

ADVERTISEMENT

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.

Code:
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:

Code:
{=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.

Code:
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:

Code:
{=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
ABCDEFGH
1Total Count:434
2312412212Max223
3912712312Window:444
4512120412Target:120312512
5120120612221
6212512112212
7512412912203
8120120120202
9912512912103
10612412112102
11312120812211
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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

Code:
=RollingMax(A2:C11,G3,G4)

Hope this helps
PGC

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

Code:
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
ABCDEFGH
1
2312412212
3912712312Target:120
4512120412Period:5
5120120612MaxResult:3
6212512112
7512412912
8120120120
9912512912
10612412112
11312120812
12
13
Sheet3
 

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
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

Board Regular
Joined
Aug 28, 2006
Messages
165
Hope this helps
PGC

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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.

Code:
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
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,545
Latest member
TWR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top