3

I need a formula in Google Sheets to count the number of rows between each positive number in a column.

In Column B of the table below, for every value in Column A that is less than, or equal to zero, <=0, I want the text NA. For any row where Column A is greater than zero, >0, I want the total number of rows between the previous row that also contained a number >0 (or the first row if there is none prior) up to and including the current row.

For example,

  1. B1="NA" because A1<=0
  2. B2="NA" because A2<=0
  3. B3=3 because A3>0 and ROWS(A1:A3)=3
  4. B4=1 because A4>0 and ROWS(A4)=1
  5. B8=4 because A8>0 and ROWS(A5:A8)=4
A B
1 -1 NA
2 -1 NA
3 1 3
4 1 1
5 -1 NA
6 -1 NA
7 -1 NA
8 1 4

What formula can I copy down Column B that will return these values?

1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Apr 13 at 14:07

3 Answers 3

6

Use reduce(), like this:

=reduce(tocol(æ, 2), A2:A, lambda(a, c, let( 
  na, "NA", 
  pattern, "[" & na & "]*$", 
  streak, regexextract(join("", a), pattern), 
  vstack( 
    a, 
    ifs( 
      isblank(c), iferror(ø), 
      c < 0, na, 
      true, ifna(len(streak) / len(na)) + 1 
    ) 
  ) 
)))

The formula goes in cell B2 and fills all of the column in one go.

Column A Column B
-1 NA
-1 NA
1 3
1 1
-1 NA
-1 NA
-1 NA
1 4

See reduce(), lambda(), let(), regexextract(), vstack() and ifs().

5
  • Thank you for your reply! Btw I did copied to my spreadsheet and it doesn't work... This formula is super complex to my knowledge, is it possible to make it simpler? Also I want the formula that only paste on the cell and let me manually copy for the whole column, instead of fills all the columns automatically. Thx again! Apr 13 at 17:15
  • oh it actually works, I should NOT change the range to sth like A$2:A, omg this kind of formula is so out of my league! Apr 13 at 17:19
  • Still hoping to have a version that the formula for each cell instead of automatically fill up the rest, as some data were incorrect and I would like to manually change a number to it! Apr 13 at 17:21
  • Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead. Apr 13 at 21:42
  • I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question? Apr 13 at 21:42
4

doubleunary's formula is a good one. If you don't want an array formula, here is a single formula that can be copied down column B stating in B1:

=IF(A1>0, IF(ROW()<>1, ROW()-IFNA(XMATCH(TRUE, INDEX(OFFSET(A$1, 0, 0, ROW()-1)>0), 0, -1))), "NA") 

The same formula works as well for row 1 as for row 2.

  1. Checks if column A has a positive number.
  2. If yes, and it isn't first row, uses XMATCH to find the position of the last positive number that precedes the current row.
  3. Then subtracts that row number returned by XMATCH from the current row number to get the number of steps.
  4. If yes, but it's row 1, returns 1.
0
3

This is "amateur standard" (my level) but may fill the bill.

Formula for cell [B 1]: =if(A1<0,"NA",row()-0)

Formula for cell [B 2]: =if(A2<0,"NA",row()-sum($B$1:$B1))

Replicating [B 2] to fill more cells of column B will auto-increment the column A row references, and $B$1:$B1 to, for instance, $B$1:$B2, $B$1:$B3, ... so the range being summed extends from the top of the column to the cell above the current cell.

I am sure more advanced techniques could be used to better specify the range of interest.


Advice: Exercise caution about using a $50 solution that "appears to work" but requires skills that are "above your pay-grade." Your employer/client may return tomorrow with extra requirements, expecting to see these requirements be fulfilled, too, simply integrated into "your" current solution. "Dial-A-Friend" may let you down.

If you don't actually know how to spin straw into gold, but portray that you have that ability, you're setting yourself up to be taught a potentially massive-and-costly lesson in morals and ethics.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.