# Thread: find 1st value GT 0

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

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

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

fburg (2014-03-06)

4. Thanks. I knew I could count on you for a VBA solution.

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

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

11. 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. 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. Thanks again Steve.

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

15. 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
•