Return relative position of Nth non-zero value in a range

Question

I have been trying to come up with a way to return the relative position of the nth non-zero value in a row range within Google Sheets. I have asked this question on Reddit too (https://www.reddit.com/r/sheets/comments/130v500/how_to_find_the_position_of_nth_nonzero_value_in/), and a helpful person provided a script to create a corresponding function to do this; however, I cannot get this to work when copying it over to Apps Script.

What I was provided was:

Returns "nth" non-zero value position, in "range"(single row or column)

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), "") ) NTH_NZPOS({0,0,5,0,0,8,0},1) => 3 NTH_NZPOS({0,0,5,0,0,8,0},2) => 6 NTH_NZPOS({0,0,5,0,0,8,0},3) => ""

Which I have tried to put into Apps Script as below, which results in several errors.

function NTH_NZPOS(range,nth)
 {
  return =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 completely lost for how to adjust this code to work. Any solution that returns the relative position of the Nth non-zero value would be great, it doesn't have to involve a custom function (ideally working within the pre-built functions that come with Sheets would be ideal)

Inputting the function above into Apps Script results in several errors. Deleting the highlighted errors of course doesn't result in a working function.

Do NOT share [spreadsheets](//meta.stackoverflow.com/a/260455)/[images](//meta.stackoverflow.com/q/285551) as the only source of data, to avoid closure of the question. Make sure to add input **and** expected output as **plain text table** to the question. [Click here](//webapps.stackexchange.com/a/161855) to create a table easily, which are **easier to copy/paste as well**. Also, note that [your email address can also be accessed by the public](//meta.stackoverflow.com/q/394304), if you share Google files. — TheMaster, Apr 28 '23 at 19:27

score 1 · Accepted Answer · answered Apr 28 '23 at 20:52

To use this function:

Click on Data then Named Functions in the top menu.
Name your function NTH_NZPOS, provide the Argument placeholders values of range & nth, then copy/paste the below formula into the Formula definition section.

=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), "") )

When you're done it should look like this:

Then to use it you just treat it like a regular function and enter =NTH_NZPOS() into a cell, provide the range, a number for which nth non-zero value you want the position of and you're good to go!

Return relative position of Nth non-zero value in a range

1 Answers1