Formula to return relative position of nth non-zero value breaks for n > 2?

Question

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!

score 1 · Answer 1 · answered Apr 28 '23 at 23:06

1

With the seven input rows in A1:G3, you can generate the desired last three columns from cell H2 using the following:

=byrow(A2:G3,lambda(row,filter(sequence(1,7),row<>0)))

answered Apr 28 '23 at 23:06

The God of Biscuits

2,029
2
3
10

Filtering is a neat idea, but wouldn't it only work in the particular case of there being three non-zeroes? – Tom Sharpe Apr 29 '23 at 10:31
1

Should work for any number of non-zeroes, I think... If the OP only wanted the first three (not totally clear from the question) then this could be achieved with ARRAY_CONSTRAIN... – The God of Biscuits Apr 29 '23 at 14:09
By "work" I suppose I meant " produce exactly three columns" so yes, array_constrain if necessary. – Tom Sharpe Apr 29 '23 at 14:51

score 1 · Answer 2 · answered Apr 28 '23 at 23:08

When debugging a formula, the first thing you should do is to remove all the IFERROR functions to see what error it is returning.

I recommend using FILTER instead of REDUCE.

Arguments

range
nth

=LET(_range,TOCOL(range),
     IFERROR(INDEX(
               FILTER(SEQUENCE(ROWS(_range)),_range<>0),
               nth)))

Chris Strickland · Answer 3 · 2023-04-29T14:01:24.170

You could generate an array that has the column numbers and the data in it, then use QUERY to find the non-zero columns, with LIMIT 1 OFFSET x to return the desired one. I copied your data, and duplicated it to test.

=query(
  {sequence(7), transpose($A7:$G7)}, 
  "SELECT Col1 WHERE Col2 <> 0 LIMIT 1 OFFSET 0"
)

This should work for any arbitrary number of non-zero values, but I believe QUERY throws an exception if it returns empty, so you'd need to catch that.

The OFFSET here is hardcoded, and I manually changed it to 1 and 2 to produce the next two columns, but it could be dynamically generated, just not sure of your actual requirements so I left it in its simplest form. For instance, here is a lambda implementation that accepts the range and generates the array and offset from that.

=lambda(rng, query(
  {sequence(columns(rng)), transpose(rng)}, 
  "SELECT Col1 WHERE Col2 <> 0 LIMIT 1 OFFSET " 
  & column() - columns(rng) - 1
))($A7:$G7)

And if the data doesn't start in column 1:

=lambda(rng, query(
  {sequence(columns(rng)), transpose(rng)}, 
  "SELECT Col1 WHERE Col2 <> 0 LIMIT 1 OFFSET " 
  & column() - columns(rng) - column(rng)
))($B7:$H7)

score 0 · Accepted Answer · answered Apr 29 '23 at 03:21

0

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

answered Apr 29 '23 at 03:21

aboyd

3
3

Formula to return relative position of nth non-zero value breaks for n > 2?

4 Answers4

Arguments