Results 1 to 9 of 9
  1. #1
    Lounger
    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    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.
    Attached Files Attached Files

  3. #3
    Uranium Lounger
    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.
    Attached Files Attached Files
    Legare Coleman

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

  5. #5
    Uranium Lounger
    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

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

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

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

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

Posting Permissions

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