Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
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 On Monday, July 23, 2012 1:09:14 PM UTC-4, JAgger1 wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
On Jul 23, 10:14*pm, wrote:
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 On Monday, July 23, 2012 1:09:14 PM UTC-4, JAgger1 wrote: 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.- Hide quoted text - - Show quoted text - Yes, I'm trying to get a row count (in this case it's 4 rows) between the two values (3), |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
On Jul 24, 7:22*am, JAgger1 wrote:
On Jul 23, 10:14*pm, wrote: 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 On Monday, July 23, 2012 1:09:14 PM UTC-4, JAgger1 wrote: 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.- Hide quoted text - - Show quoted text - 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
On Jul 24, 9:36*am, Claus Busch wrote:
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
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 On Tuesday, July 24, 2012 7:56:09 AM UTC-4, JAgger1 wrote: 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, than |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
On Jul 24, 11:39*am, wrote:
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 On Tuesday, July 24, 2012 7:56:09 AM UTC-4, JAgger1 wrote: 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, than- Hide quoted text - - 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
How to you select the threes? what would be different if you wanted 4's?
On Tuesday, July 24, 2012 11:56:52 AM UTC-4, JAgger1 wrote: On Jul 24, 11:39*am, wrote: > 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 > > > > On Tuesday, July 24, 2012 7:56:09 AM UTC-4, JAgger1 wrote: > > 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, than- Hide quoted text - > > - 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
The way Claus has it written in his formula, B1 & B2
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
On Mon, 23 Jul 2012 10:09:14 -0700 (PDT), JAgger1 wrote:
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. How about a User Defined Function? To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this User Defined Function (UDF), enter a formula like =MaxBetweenNum(rng,Nbr) where Rng is the cell range to search, and Nbr is the number you are searching for. As per your example, the result is the maximum row count "between" the duplicate Nbr's (excluding from the count both the rows where Nbr actually exists). Also, if Nbr is not a duplicate (or is non-existent), the function will return a #NUM! error. =============================================== Option Explicit Function MaxBetweenNum(rg As Range, Nbr As Long) As Variant Dim c As Range Dim sFirstAddress As String Dim bFirstRun As Boolean Dim i As Long, j As Long With rg Set c = .Find(what:=Nbr, after:=rg(.Rows.Count), _ LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, _ searchdirection:=xlNext) If Not c Is Nothing Then i = 0 j = c.Row sFirstAddress = c.Address Do 'FindNext doesn't seem to work in a Function Set c = .Find(what:=Nbr, after:=c) If c.Address < sFirstAddress Then i = WorksheetFunction.Max(i, c.Row - j) j = c.Row End If Loop Until c.Address = sFirstAddress End If End With MaxBetweenNum = i - 1 If i = 0 Then MaxBetweenNum = CVErr(xlErrNum) End Function ======================================== |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
Hi Ron
I created the UDF as you suggested, but I keep getting #NAME? error when I try to use it? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
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. " |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Count Between Value
On Wed, 25 Jul 2012 09:15:34 -0700 (PDT), JAgger1 wrote:
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. " That line SHOULD be there. Maybe you had it there twice? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to COUNT the pairing of DIGITS in a list, cell by cell | Excel Worksheet Functions | |||
count duplicats, display incremental count, restart count at changein value | Excel Programming | |||
count cells, then reset count when value in another cell changes | Excel Worksheet Functions | |||
How to auto count data in an empty cell to be 0. Blank cell=0 | Excel Discussion (Misc queries) | |||
Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? | Excel Discussion (Misc queries) |