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!
