# Thread: Counting 3 or more in a series (97/2000/XP)

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

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

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

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

5. ## 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?

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

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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•