Results 1 to 9 of 9

20020816, 16:42 #1
 Join Date
 Mar 2002
 Location
 All Over, CA, USA
 Posts
 29
 Thanks
 0
 Thanked 0 Times in 0 Posts
Counting 3 or more in a series (97/2000/XP)
I have what I assume is a simple question, though the I am having trouble guessing the simplest approach.
I have a series of numbers in a single column, B2:B25, that are both positive and negative values.
I need a formula to count the number of times there are 3 or more positive values in a series.
Sample data is attached.

20020816, 16:52 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Counting 3 or more in a series (97/2000/XP)
I'm sure that a member of the array formula gang will come up with one array formula in one cell, but here is a solution using an intermediate column:
Compute if a cell and its two predecessors are positive, and it's successor not positive. Count the number of TRUE values in this column. See attached workbook.

20020816, 17:18 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting 3 or more in a series (97/2000/XP)
I think that you will need a User Defined Function to do this. I have attached your workbook with a function included that I think does what you want.
Legare Coleman

20020816, 19:40 #4
 Join Date
 Mar 2002
 Location
 All Over, CA, USA
 Posts
 29
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting 3 or more in a series (97/2000/XP)
Thanks for the UDF.
I was getting mixed results when I change a few numbers from Plus to minus, but it seems to be me. It does not always recalculate. If I drag the formula down from the first cell, after I make the chages, it recalcs again and the answers are correct.
do you know why it id doing this? Autocalculation is on and I tried hitting F9 but it did ot recalc.

20020816, 20:06 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting 3 or more in a series (97/2000/XP)
I didn't have any problems when I tested. Can you explain exactly what you did?
Legare Coleman

20020816, 20:47 #6
 Join Date
 Mar 2002
 Location
 All Over, CA, USA
 Posts
 29
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting 3 or more in a series (97/2000/XP)
I changed the value in B11 from a 1 to a +1 and the 1 in C10 did not change to "". It did add the 1 to C13 as it should.
Puzzled, I hit F9 to see if it was not calculating, but it did not change anything. I looked over your UDF and could not find any problem. I know enough to be dangerous, but your code seems very straight forward and I can found no reason for the error. I am sure the problem is on my end.
If I hit F2 to edit the cell, then hit Enter without making a change, the cell recalculates correctly.
I have tried closing the worksheet and reopening it. It does the same thing consistently. I am stumped.

20020816, 23:36 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting 3 or more in a series (97/2000/XP)
Yup, I see the problem. If you change a cell that affects a cell, but is not in the range of cells passed to the function, the cell is not recalculated. The problem can be fixed by adding one like to the function:
<pre>Option Explicit
Public Function CountSeries(oRng As Range) As Variant
Dim iCnt As Integer
Dim oCell As Range, oLast As Range
Application.Volatile
iCnt = 0
For Each oCell In oRng
If oCell.Value > 0 Then
iCnt = iCnt + 1
Else
iCnt = 0
End If
Set oLast = oCell
Next oCell
If iCnt >= 3 And oLast.Offset(1, 0) <= 0 Then
CountSeries = 1
Else
CountSeries = ""
End If
End Function
</pre>
Legare Coleman

20020817, 00:09 #8
 Join Date
 Mar 2002
 Location
 All Over, CA, USA
 Posts
 29
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting 3 or more in a series (97/2000/XP)
I was feeling volatile, but all is great now.
From the Help file:
A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change.
It sounds like this is a trick to force your UDF to recalculate anytime any calculation occurs.
Thanks a lot for your help.

20020901, 17:48 #9
 Join Date
 Mar 2002
 Location
 All Over, CA, USA
 Posts
 29
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting 3 or more in a series (97/2000/XP)
A longer formula, but a simpler approach
Add a column C with this formula
=IF(AND(ISNUMBER(B1),ISNUMBER(B2)),AND(B1>0, B2>0, B3>0),FALSE)
This makes sure that the first to cells are numbers and returns FALSE for the bad references at the top of the list.
Copy the formulas down as many rows as there are, then =COUNTIF(C1:C25, TRUE) works