How to find the value in a cell after consecutive positive numbers

user_16461395 0
Viewed 76

enter image description here

I have a list here of numbers, positive and negative, from A3:A17. I want to find the value after a consecutive streak of positive numbers. So in the picture above, there's a streak of two consecutive positive numbers from A5 to A6, 2.60% and 1.10%, respectively. I want to get the number after that, which is -1.90%. But if there's another streak of two positive numbers, I want the formula to be able to identify that too. I have a formula to identify the streak but not one to identify the number that comes after it.

google-sheets-formula google-sheets-api google-sheets
3 Answers

Try these in cell B3 (deleting everything below):

Any occurrences:

=arrayformula(if(A3:A<0,if(A2:A>0,if(A1:A>0,A3:A,),),))

2 occurrences:

=arrayformula({"";if(A4:A<0,if(A3:A>0,if(A2:A>0,if(A1:A<0,A4:A,),),),)})

3 occurrences:

=arrayformula({"";"";if(A5:A<0,if(A4:A>0,if(A3:A>0,if(A2:A>0,if(A1:A<0,A5:A,),),),),)})

4 occurrences:

=arrayformula({"";"";"";if(A6:A<0,if(A5:A>0,if(A4:A>0,if(A3:A>0,if(A2:A>0,if(A1:A<0,A6:A,),),),),),)})
user_157844150 
=ArrayFormula(QUERY(IF(A3:A>0,IF(A4:A>0,A5:A,),),"select Col1 where Col1 is not null",0))
user_161251390

Try this in cell B3 and autofill downward:

=IF(A1>0,IF(A2>0,A3,""))
user_62454730
Related