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