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.
To use this function:
Data
then Named Functions
in the top menu.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!