google-sheetsnamed-function

Formula to return relative position of nth non-zero value breaks for n > 2?


I have been trying to put together a named function in Google Sheets that will output the relative position of a cell within a single row that contains the nth non-zero value.

Day1 Day2 Day3 Day4 Day5 Day6 Day7 1st Non-0 2nd Non-0 3rd Non-0
12 0 0 14 0 0 8 1 4 7
0 16 0 0 23 0 17 2 5 7

The first 7 columns in this example are my input, the last 3 columns are my desired output.

I have added a named function to Google Sheets that successfully does this task for the 1st and 2nd non-zero values, but for the 3rd and beyond, it returns an empty cell. The function is:

NTH_NZPOS(range,nth)
=let(s,tocol(range),
  iferror(
    index(
      reduce(,sequence(rows(s)),
        lambda(a,i,if(ne(index(s,i,1),0),if(isblank(a),{i},{a;i}),a))),
    nth,1),
  "")
)

I am sure that there is a small error in the above code preventing this function from working for n > 2, but I cannot seem to find the issue.

Thank you!


Solution

  • I needed to replace index with chooserows, working function is this:

    NTH_NZPOS(range,nth) =let(s,tocol(range), iferror( chooserows( reduce(,sequence(rows(s)), lambda(a,i,if(ne(index(s,i,1),0),if(isblank(a),{i},{a;i}),a))), nth), "") )