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

 

[tx12345]

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.

shoot, that is amazing. i would be pretty embarrassed if i told you how i was getting to that result before.

thanks again. my very fat spreadsheet is going to lose some weight today!

tx

 

[Domenic]

... 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.

Assuming that A2:C31 contains the data, E2 the target number, such as 120, and F2 contains the rolling number, such as 5, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=MAX(COUNTIF(OFFSET(A2:C31,ROWS(A2:C31)-(ROW(INDIRECT("1:"&INT(ROWS(A2:C31)/F2)))*F2-F2)-1,,-F2),E2))

Note that in this example the formula will look at every five rows, starting at the bottom, and work it's way up. Also, the formula won't evaluate a partial set, as would be the case if the rolling number was set to 4. In this case, Rows 2 and 3 would not be taken into account. Is that okay?

 

[tx12345]

yes, excellent, works great. thanks for your insight. it is much appreciated


tx

... 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.

Assuming that A2:C31 contains the data, E2 the target number, such as 120, and F2 contains the rolling number, such as 5, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=MAX(COUNTIF(OFFSET(A2:C31,ROWS(A2:C31)-(ROW(INDIRECT("1:"&INT(ROWS(A2:C31)/F2)))*F2-F2)-1,,-F2),E2))

Note that in this example the formula will look at every five rows, starting at the bottom, and work it's way up. Also, the formula won't evaluate a partial set, as would be the case if the rolling number was set to 4. In this case, Rows 2 and 3 would not be taken into account. Is that okay?