Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    find 1st value GT 0

    I'm attaching a mocked-up workbook showing what I want and hopefully it's clear.

    col B has values: -1 represents a "null" value, positive numbers represent a valid value.

    for a given row, if col B is -1, then col A should be -1. If col B is positive, I want col A to contain the col B entry of the next positive col B value. In other words, skip any col B rows with -1 and return the first col B value that is positive.

    Looking for a formula, not VBA, to do the above.

    TIA

    Fred
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    fburg,

    The VBA approach (of course) using a UDF. Enter in Cell A13 =SequentialNums(B13) and copy down.

    SequentialNums.png

    Code:
    Public Function SequentialNums(rng As Range) As Double
    Application.Volatile
    If rng.Value < 1 Then SequentialNums = -1
    counter = 1
    If rng >= 1 Then
        Do While rng.Offset(counter, 0) = -1
            counter = counter + 1
        Loop
        If rng.Offset(counter, 0) = "" Then
            SequentialNums = -1
        Else:
        SequentialNums = rng.Offset(counter, 0)
        End If
    End If
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2014-03-05 at 22:38. Reason: forgot to add Application.Volatile to the code

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    fburg (2014-03-06)

  4. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks. I knew I could count on you for a VBA solution.

    Let's see if anyone comes up with an Excel formula.

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To get A values from the B column will require an intermediate column

    In C1 enter:
    =ROW(B1)-COUNTIF(B$1:B1,"<0")
    copy it down the range

    In A1:
    =INDEX($B$1:$B$11,MATCH(2,$C$1:$C$11,0))

    In A2:
    =IF(COUNTIF(A$1:A1,">0")=COUNTIF(A$1:A2,">0"),-1,INDEX($B$1:$B$11,MATCH(COUNTIF(A$1:A2,">0")+1,$C $1:$C$11,0)))
    Copy it down the rest of the rows.

    Steve

  6. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Steve,

    Thks for the formula.

    2 things about the A2 formula:
    - a minor typo? - towards the end, there is a space between $C and $1. Easy enough to fix
    - Excel complains about a circular reference. The last formula placed in A2 has part of its condition COUNTIF(A$1:A2...). Later on in the formula, the same ...A2 appears. This is what's creating the circular reference. Not sure if that's what you intended. I'm wondering if col A should be col B??

    I kind of get what the formula is doing: you're looking for the row in the B range based on the next pos # as given by the values in col C. I'm not totally sure about the test condition (even given the circular reference). This seems a little similar to something you and I iterated on a few months ago where I was filling another sheet based on searching for the next Y using the RANK BIF.

    Fred

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Sorry, Brain fart. I built the formula in another column and compared the values, not thinking enough about hte logic when put into the column.
    Try thhis in A2:
    Code:
    =IF(OR(B2=-1,COUNTIF(A$1:A1,">0")>=COUNTIF($B$2:$B$11,">0")),-1,INDEX($B$1:$B$11,MATCH(COUNTIF(A$1:A1,">0")+2,$C$1:$C$11,0)))
    And yes the space is wrong, sometimes spurious spaces get put into formulas when copying as text. I put it into a code box and this should have less issues. Is this one more clear in the logic? The countif comparison is only used for the last item to get -1 when the list is ended, otherwise you will keep getting the last positive value.

    Steve

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    fburg (2014-03-07)

  9. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Steve,

    Yep - this worked better. Thks.

    A couple of things:
    - I noticed in the newest formula that the MATCH argument had MATCH(...+2...) whereas the first iteration had +1. Why the change? Was this for the circular reference? I can see it works with +2; I never checked with +1 bcs of the circular reference error.
    - I think I got the logic of the first COUNTIF comparisons: looking at how many pos values have already occurred above the current row (starting with A2). If that's equal to the total # of pos values in B starting at row 2, then we are at the end of the range. So the > part of the comparison should never come into play. Yes?
    - I do see how the proper value from B is being filled into A thru the #s in C.

    Thks again.

    Fred

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    fburg,

    Here are some alternative formulas:

    in A1 enter =IF(B1>=1,IF(B2="",-1,IF(OFFSET(B1,C2,0)="",-1,OFFSET(B1,C2,0))),-1)
    in C1 enter {=MATCH(TRUE,B1:B11<>-1,0)}

    NOTE: The formula for C1 is an array formula
    Copy down both columns
    Attached Files Attached Files
    Last edited by Maudibe; 2014-03-08 at 06:11.

  11. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The +2 comes from +1 since you start with the 2nd, not the first item, and the 2nd +1 is because you don't count the row you are on (and yes doing that is what caused the circular reference).

    And Yes you are right, The > should not come into play as the number of pos entries should not be larger than the total number of positive entries...

    Steve

  12. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Maudibe,

    Thks for the formulaic approach.

    One question: isn't examining cells beyond the range a little dangerous? I noted the formula for col C adjusts for relative cells as I fill down. So when I get to cell C11, the MATCH is examining the range B11:B21.

    Fred

  13. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks again Steve.

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Since it wasn't clear if the range would always stop at row 11 I coded it that way. As an alternative, you could simply replace C1 with:

    {=MATCH(TRUE,B1:B$12<>-1,0)}

    (addition of $) then copy down to C11

    gto3.png
    Last edited by Maudibe; 2014-03-08 at 11:31.

  15. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Maudibe,

    The spreadsheet I attached was a mockup so the real one would not stop at row 11. But it would stop at some other row, beyond which one should not depend on the contents. I think there is a way, based on other counters in the real spreadsheet, to put in a stopping condition.

    But I noticed that your revised formula not only added the $ but also changed B11 to B12.

    BTW: I never saw at MATCH(TRUE...) statement. I always match on some cell or value. Learn something new each day.

    Thanks.

    Fred

Posting Permissions

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