Search code examples
google-sheetsgoogle-sheets-formulanamed-function

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

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.

Solution

  • To use this function:

    1. Click on Data then Named Functions in the top menu.
    2. 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:

    Named Functions

    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!