Results 1 to 13 of 13
Thread: find 1st value GT 0

20140305, 21:36 #1
 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 mockedup 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

20140305, 22:27 #2
 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
Last edited by Maudibe; 20140305 at 22:38. Reason: forgot to add Application.Volatile to the code

The Following User Says Thank You to Maudibe For This Useful Post:
fburg (20140306)

20140306, 10:44 #3
 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.

20140306, 11:35 #4
 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

20140307, 09:45 #5
 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

20140307, 10:44 #6
 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)))
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
fburg (20140307)

20140307, 21:17 #7
 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

20140308, 02:45 #8
 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 columnsLast edited by Maudibe; 20140308 at 06:11.

20140308, 07:45 #9
 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

20140308, 08:29 #10
 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

20140308, 08:30 #11
 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.

20140308, 11:20 #12
 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.pngLast edited by Maudibe; 20140308 at 11:31.

20140309, 10:30 #13
 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