Return index of the smallest non-zero entry in an array

[PHMayfield]

I am looking for a formula-based solution so I can put this into a larger formula. Here is a sample:

      ColA
Row1   0
Row2   3
Row3   4
Row4   0
Row5   1

I want to return "5" in reference to the row number. All entries will be positive or zero, but I don't know how many zeros there may be on a given list.

Thanks!

[ This Message was edited by: PHMayfield on 2002-05-17 10:57 ]

[Aladin Akyurek]

On 2002-05-17 10:56, PHMayfield wrote:
I am looking for a formula-based solution so I can put this into a larger formula. Here is a sample:

      ColA
Row1   0
Row2   3
Row3   4
Row4   0
Row5   1

I want to return "5" in reference to the row number. All entries will be positive or zero, but I don't know how many zeros there may be on a given list.

Thanks!

[ This Message was edited by: PHMayfield on 2002-05-17 10:57 ]

Array-enter:

=MATCH(MIN(IF(A1:A5,A1:A5)),A1:A5,0)

To array-enter a formula, you need to hit control+hift+enter at the same time, not just enter.

This will give the "index" of the first instance of the non-zero min value.

Aladin

[Asala42]

Hey,

Based on your example, how about:

=MATCH(SMALL(A1:A5,1+COUNTIF(A1:A5,0)),A1:A5,0)

You may have to tweak the formula a bit if your data does not start on row1. If your data started on row 6, for example, you'd want to add 5 at the end of the formula to get the correct row number.

Hope that helps,
Adam

[PHMayfield]

Thanks, that worked great.

I should say, both work fine.

[ This Message was edited by: PHMayfield on 2002-05-17 11:19 ]

[Yogi Anand]

The following array formula will also work:

=MATCH(SMALL(A1:A5,SUM(IF(A1:A5=0,0,1))),A1:A5,0)

Regards!