Searching A List

  • Thread starter Thread starter smartin
  • Start date Start date
S

smartin

Given the following in a column:

Number
0
1
1
0
0
1
0
0
0
0

I would like, in a single cell (no helper cells), to return the
position of the last non-zero value in my list (which begins below the
column header). In this case the correct answer is 6.

This is more of a curiousity/challenge question for me at this point
since I found a work-around using helper cells.

I think the heart of the problem, and why I even bring it up, is we
need to know how to tap into array indices, or reverse of the order of
a one-dimensional array in place. Any ideas welcomed.
 
Given the following in a column:

Number
0
1
1
0
0
1
0
0
0
0

I would like, in a single cell (no helper cells), to return the
position of the last non-zero value in my list (which begins below the
column header). In this case the correct answer is 6.

This is more of a curiousity/challenge question for me at this point
since I found a work-around using helper cells.

I think the heart of the problem, and why I even bring it up, is we
need to know how to tap into array indices, or reverse of the order of
a one-dimensional array in place. Any ideas welcomed.
Click to expand...

And of course, I figured out this array formula 20 minutes later:

{=MAX(--(LIST<>0)*ROW(LIST))-n}

where n is the number of row headers
 
Back
Top