counting instances of 'x' in a range efficiently

[tx12345]

hi

let's say i have this set of numbers

105 120 276 <--120 shows up here
212 350 274
140 370 286
310 336 276
250 331 296
270 120 376 <--120 shows up here
280 310 216
289 120 215 <--120 shows up here
280 330 216
299 250 212

3 columns wide (fixed), and x rows deep (varies), in this case 10

i am trying to find the quickest way possible to get the result, avoiding fill down formula. maybe a single cell formula can do this? a macro?

find:

1) the last time any given number appeared (the bottom number being the very last appearance), for example, '120' shows up three rows ago

2) the longest stretch of rows the number fails to appear; for example, the '120's last time to appear was 3 rows up. the time before that 2 rows between appearances, before that 5. so looking at the data the longest stretch of '120' not appearing was 5 rows.

3) the number of times any particular number might appear in any particluar number of 'rolling' rows. for example the number to target will be 276, and the 'rolling' number of rows will be set to 4


i have been doing it the long way im sure. there has to be a better way. any thoughts are very appreciated!

tx

 

[xl_psychic]

tx,

Answer to your 3rd query is

=COUNTIF(OFFSET($B$2,0,0,$G$2,3),G3) Where

G3 houses the value you need to find
G2 houses the no. of rows you need to include in your data range

If you want the columns to be dynamic replace the 3 in the above formula with a cell reference.

Hope this helps.

 

[tx12345]

thanks for the reply, but i am not sure i get the formula

how does it apply to a range of cells located at, let's say, a1:c10?

thanks

 

[Domenic]

1) the last time any given number appeared (the bottom number being the very last appearance), for example, '120' shows up three rows ago

Assuming that A2:C11 contains the data, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=ROWS(A2:C11)-MATCH(2,1/(MMULT(--(A2:C11=G2),TRANSPOSE(COLUMN(A2:C11)^0))>0))+1

...where G2 contains the number of interest, such as 120.

2) the longest stretch of rows the number fails to appear; for example, the '120's last time to appear was 3 rows up. the time before that 2 rows between appearances, before that 5. so looking at the data the longest stretch of '120' not appearing was 5 rows.

If we have the following data...

105	125	276	
212	350	274	
140	370	286	
310	336	276	
250	331	296	
270	120	376	<--120 shows up here
280	310	216	
289	120	215	<--120 shows up here
280	330	216	
299	250	212

...what should the correct result be? If the correct result is 3, try the following formulas which also need to be confirmed with CONTROL+SHIFT+ENTER...

J2:

=MATCH(TRUE,MMULT(--(A2:C11=G2),TRANSPOSE(COLUMN(A2:C11)^0))>0,0)

K2:

=IF(COUNTIF(A2:C11,G2),MAX(FREQUENCY(IF(ROW(A2:C11)-ROW(A2)+1>=J2,IF(MMULT(--(A2:C11=G2),TRANSPOSE(COLUMN(A2:C11)^0))=0,ROW(A2:C11))),IF(MMULT(--(A2:C11=G2),TRANSPOSE(COLUMN(A2:C11)^0)),ROW(A2:C11))))+1,#N/A)

3) the number of times any particular number might appear in any particluar number of 'rolling' rows. for example the number to target will be 276, and the 'rolling' number of rows will be set to 4

Using the previous data posted, if the 'rolling' number of rows is set to 5, what should the correct result be? If it's 1, try...

=SUMPRODUCT(--(COUNTIF(OFFSET(A2:C11,ROW(INDIRECT("1:"&ROWS(A2:C11)/M2))*M2-M2,,M2),G2)>0))

...where M2 contains the 'rolling' number of rows, such as 5. If the number of rows that the data contains isn't evenly divisable by the set 'rolling' number of rows, in this case 5, replace...

ROWS(A2:C11)/M2

with

ROUNDUP(ROWS(A2:C11)/M2,0)

Hope this helps!

 

[Chas17]

prog_9_15.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1
2						myNum	120		3	6	xl_psychic
3	RowCount	10
4	myRngeRef	105	120	276		LastTime	3	timesago
5		212	350	274		MaxStretch	4
6		140	370	286
7		310	336	276
8		250	120	296
9		270	120	376
10		280	310	216
11		289	120	215
12		280	330	216
13		299	250	212
14
Sheet4

Code for Go Button:

Sub Stats()
'9/15/2006 by Charles R. Weber

Dim cell As Range
Dim myNum As Integer
Dim LastTime As Long, NumRows As Long, StartStretch As Long, MaxStretch As Long

myNum = Range("myNum")
NumRows = Range("RowCount")

Range("LastTime").Clear
LastTime = 0
Range("MaxStretch").Clear
MaxStretch = 0
StartStretch = 0
Range("myRnge").Interior.ColorIndex = xlNone

For Each cell In Range("myRnge")

    If cell = myNum Then
        cell.Interior.Color = vbYellow
        If cell.Row - StartStretch > MaxStretch Then
            MaxStretch = cell.Row - StartStretch
        End If
        StartStretch = cell.Row
        LastTime = cell.Row
    End If
    
Next cell

Range("LastTime") = Range("myRngeRef").Row + Range("RowCount") - LastTime
Range("MaxStretch") = MaxStretch
End Sub

myRnge is a dynamic range
=OFFSET(myRngeRef,0,0,RowCount,3)
Other range names shown on worksheet, e.g. RowCout is cell B3

Chas

 

[tx12345]

the first two are perfect. thanks!

the last condition i think needs a little tweaking: ley me rephrase the question


using the same dataset:

105   125   276   
212   350   274   
140   370   286   
310   336   276   
250   331   296   
270   120   376   <--120 shows up here
280   310   216   
289   120   215   <--120 shows up here
280   330   216   
299   250   212

with the last row the most recetn, with a rolling period of 5 rows, there are six 'sets' of five rows:

1
105   125   276   
212   350   274   
140   370   286   
310   336   276   
250   331   296   

2
212   350   274   
140   370   286   
310   336   276   
250   331   296   
270   120   376  <--120 shows up here

3
140   370   286   
310   336   276   
250   331   296   
270   120   376   <--120 shows up here
280   310   216  

4
310   336   276   
250   331   296   
270   120   376   <--120 shows up here
280   310   216   
289   120   215   <--120 shows up here

5
250   331   296   
270   120   376   <--120 shows up here
280   310   216   
289   120   215   <--120 shows up here
280   330   216   

6
270   120   376   <--120 shows up here
280   310   216   
289   120   215   <--120 shows up here
280   330   216   
299   250   212

so the maximum number of time 120 appears in any of the 'rolling' sets of 5 rows is 2.

i think now you have a better idea of the question.

thanks again. the other formulas are amazing.

tx

 

[tx12345]

hello chas,

sweet macro, thanks very much for the insight.

the offset is great too. i have a question about the offset:

{=COUNTIF(OFFSET(B4:D13,0,0,G7,3),G2)}

it does a good job of counting an aggregate total. i am also interested in the 'rolling' total, along the lines of my last post.

thanks again!

tx

 

[Domenic]

the first two are perfect. thanks!

You're very welcome!

the last condition i think needs a little tweaking...

I had a feeling that that might be the case...

with the last row the most recetn, with a rolling period of 5 rows, there are six 'sets' of five rows:

1
105   125   276   
212   350   274   
140   370   286   
310   336   276   
250   331   296   

2
212   350   274   
140   370   286   
310   336   276   
250   331   296   
270   120   376  <--120 shows up here

3
140   370   286   
310   336   276   
250   331   296   
270   120   376   <--120 shows up here
280   310   216  

4
310   336   276   
250   331   296   
270   120   376   <--120 shows up here
280   310   216   
289   120   215   <--120 shows up here

5
250   331   296   
270   120   376   <--120 shows up here
280   310   216   
289   120   215   <--120 shows up here
280   330   216   

6
270   120   376   <--120 shows up here
280   310   216   
289   120   215   <--120 shows up here
280   330   216   
299   250   212

so the maximum number of time 120 appears in any of the 'rolling' sets of 5 rows is 2.

A couple of questions...

1) Is the data actually laid out in this manner, or is the data listed consecutively, without the numbering for the 'rolling' sets and blank rows?

2) Will the total number of rows for the data always be evenly divisable by the number of 'rolling' sets? If not, does the counting start from the bottom? And what happens to the partial set at the top, in this case?

 

[pgc01]

Hi tx

In this solution I defined some names to make it easier to read the formulas and also to move the numbers, the inputs and the outputs around. I included the definitions in A16:C20 just as documentation. You can change the range and you just have to adjust the name, the formulas do not change.

I defined

Names	Refer to
NLast	=Sheet1!$B$4
NRolling	=Sheet1!$B$2
Rng	=Sheet1!$D$4:$F$13
Target	=Sheet1!$B$1

The three formulas are array formulas and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

In B4:

=ROW(Rng)+ROWS(Rng)-MAX(IF(Rng=Target,ROW(Rng)))

In B5:

=MAX(FREQUENCY(IF(COUNTIF(OFFSET(Rng,-1+ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1)),0,1,COLUMNS(Rng)),Target)=0,ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1))),IF(COUNTIF(OFFSET(Rng,-1+ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1)),0,1,COLUMNS(Rng)),Target),ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1)))),NLast-1)

In B6:

=MAX(COUNTIF(OFFSET(Rng,-1+ROW(INDIRECT("1:"&ROWS(Rng)-NRolling+1)),0,NRolling,COLUMNS(Rng)),Target))

Hope this contributes to your solution
PGC

EDIT: Corrected the reference to Rng

Book2
	A	B	C	D	E	F	G	H
1	Target	120
2	NRolling	5
3
4	NLast	3		105	125	276
5	Longeststretch	5		212	350	274
6	TotalNRolling	2		140	370	286
7				310	336	276
8				250	331	296
9				270	120	376
10				280	310	216
11				289	120	215
12				280	330	216
13				299	250	212
14
15
16	Names		Referto
17	NLast		=Sheet1!$B$4
18	NRolling		=Sheet1!$B$2
19	Rng		=Sheet1!$D$4:$F$13
20	Target		=Sheet1!$B$1
21
22
Sheet1

 

[tx12345]

the first two are perfect. thanks!

You're very welcome!

the last condition i think needs a little tweaking...

I had a feeling that that might be the case...


...

3 columns wide, 360 rows deep

the rolling count will be a variable, anywhere from 5 to 60. so i guess there can be instances where dividing one into the other will result in a fraction.

does that answer your question?

thanks again


tx