Cell Count Between Value

  • Thread starter Thread starter JAgger1
  • Start date Start date
J

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.
 
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
 
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 -
Click to expand...

Yes, I'm trying to get a row count (in this case it's 4 rows) between
the two values (3),
 
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
 
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 -
Click to expand...

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?
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
 
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 -
Click to expand...



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
 
Hi Ron

I created the UDF as you suggested, but I keep getting #NAME? error
when I try to use it?
 
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.
"
 
Back
Top