counting instances of 'x' in a range efficiently

tx12345

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

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
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

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
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...

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

Well-known Member
Joined
Oct 16, 2002
Messages
657

ADVERTISEMENT

prog_9_15.xls
ABCDEFGHIJKL
1
2myNum12036xl_psychic
3RowCount10
4myRngeRef105120276LastTime3timesago
5212350274MaxStretch4
6140370286
7310336276
8250120296
9270120376
10280310216
11289120215
12280330216
13299250212
14
Sheet4


Code for Go Button:
Code:
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

Board Regular
Joined
Aug 28, 2006
Messages
165
the first two are perfect. thanks!

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


using the same dataset:

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

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

Board Regular
Joined
Aug 28, 2006
Messages
165

ADVERTISEMENT

hello chas,

sweet macro, thanks very much for the insight.

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

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
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:

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
Code:
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:
Code:
=ROW(Rng)+ROWS(Rng)-MAX(IF(Rng=Target,ROW(Rng)))

In B5:
Code:
=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:
Code:
=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
ABCDEFGH
1Target120
2NRolling5
3
4NLast3105125276
5Longeststretch5212350274
6TotalNRolling2140370286
7310336276
8250331296
9270120376
10280310216
11289120215
12280330216
13299250212
14
15
16NamesReferto
17NLast=Sheet1!$B$4
18NRolling=Sheet1!$B$2
19Rng=Sheet1!$D$4:$F$13
20Target=Sheet1!$B$1
21
22
Sheet1
 

tx12345

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

Forum statistics

Threads
1,137,330
Messages
5,680,857
Members
419,936
Latest member
rphill48

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