Cell Count Between Value

[JAgger1]

Is there a simple formula for getting cell count beween a certain
value? Example:

In column A1:A8 I have the following numbers.

1
3
6
9
6
7
3
2

I would like a formula that can give me the cell count between
A2(number3) and A7(number 3), which would be 4.

 

[KenCowen]

Are you trying to count the rows between those two cells? I am not sure what yo mean by count between values. There are no values between 3 and 3; but they happen to be 4 rows apart, so would the answer be 4? Are you always going to go with cells A2 and A7? Or is always going to be the the two threes? Please explain what you are trying to accomplish, and I am sure there will be a formula that can figure it out.
Ken

 

[JAgger1]

Are you trying to count the rows between those two cells?  I am not sure what yo mean by count between values.  There are no values between 3 and 3; but they happen to be 4 rows apart, so would the answer be 4?  Are you always going to go with cells A2 and A7?  Or is always going to be thethe two threes?  Please explain what you are trying to accomplish, and Iam sure there will be a formula that can figure it out.
Ken






- Show quoted text -

Yes, I'm trying to get a row count (in this case it's 4 rows) between
the two values (3),

 

[JAgger1]

I found this formula and it works well,

=ABS(MATCH(B1,A1:A8,0)-MATCH(B2,A1:A8))-1

By entering the value 3 in B1 and B2 I get my answer of 4.

Now if I have a larger range of values, with duplicates,

In column A1:A10 I have the following numbers.


1
3
6
9
6
7
3
2
4
3

anyone know how to modify this formula so I can get a correct answer
of 4 (A2 - A7) and 2 (A7 - A10)

I would like to have a formula that would work with a set of 100
(A1:A100) numbers with multiple duplicates, thanks

 

[JAgger1]

Yes, I'm trying to get a row count (in this case it's 4 rows) between
the two values (3),- Hide quoted text -

- Show quoted text -

Better yet, If I have a list of 100 numbers with multiple duplicate's,
can this formula:

=ABS(MATCH(BA1,A1:A100,0)-MATCH(B2,A1:A100))-1 (where B1 & B2 are the
search value, in previous case number 3)

be modified to show the largest row count between duplicates?

 

[Claus Busch]

Hi JAgger,

Am Tue, 24 Jul 2012 04:56:09 -0700 (PDT) schrieb JAgger1:

1
3
6
9
6
7
3
2
4
3

anyone know how to modify this formula so I can get a correct answer
of 4 (A2 - A7) and 2 (A7 - A10)

I would like to have a formula that would work with a set of 100
(A1:A100) numbers with multiple duplicates, thanks

try following formula (with 1. criteria in B1 and 2. in B2):
=MAX((A1:A1000=B2)*ZEILE(1:1000))-VERGLEICH(B1;A1:A1000;0)-1
and enter the array formula with CRTL+Shift+Enter


Regards
Claus Busch

 

[Claus Busch]

Hi Jagger,

Am Tue, 24 Jul 2012 15:29:16 +0200 schrieb Claus Busch:

try following formula (with 1. criteria in B1 and 2. in B2):
=MAX((A1:A1000=B2)*ZEILE(1:1000))-VERGLEICH(B1;A1:A1000;0)-1
and enter the array formula with CRTL+Shift+Enter

sorry, I posted the formula in German. Here the formula in English:
=MAX((A1:A1000=B2)*ROW(1:1000))-MATCH(B1,A1:A1000,0)-1
and enter with CTRL+Shift+Enter


Regards
Claus Busch

 

[JAgger1]

Hi Jagger,

Am Tue, 24 Jul 2012 15:29:16 +0200 schrieb Claus Busch:


sorry, I posted the formula in German. Here the  formula in English:
=MAX((A1:A1000=B2)*ROW(1:1000))-MATCH(B1,A1:A1000,0)-1
and enter with CTRL+Shift+Enter

Regards
Claus Busch

Thanks for your reply.

I tried your formula with a set of 1000 numbers with multiple
duplicate's, the result the formula gives me is the row count between
the first and last occurance, in this case I used the number 2 (A3 &
A1000) and it gave me the result of 996.

Can this be modified to show the largest row count between duplicates?
I've set up the list so that some duplicate's are right after each
other and some are a couple of row's apart and the largest is just
under 100 rows apart. Thanks

 

[KenCowen]

That seems like two right answers, 2 and 4. Is the "right" answer an array of numbers? Are you only looking for duplicate 3's? Do you want the number of rows between consecutive duplicates? Or the max difference between any two 3's.
Ken

 

[JAgger1]

That seems like two right answers, 2 and 4.  Is the "right" answer an array of numbers?  Are you only looking for duplicate 3's?  Do you want the number of rows between consecutive duplicates? Or the max difference between any two 3's.
Ken








- Show quoted text -

Hi Ken

I'd like to get the max difference in rows between any two consecutive
duplicates that I would select, in this case 3's

 

[KenCowen]

How to you select the threes? what would be different if you wanted 4's?

 

[JAgger1]

The way Claus has it written in his formula, B1 & B2

 

[JAgger1]

Hi Ron

I created the UDF as you suggested, but I keep getting #NAME? error
when I try to use it?

 

[JAgger1]

Cancel that, I got it, I had included the line "Option Explicit" and
once I removed it the UDF worked perfect. Thanks for you help.
"