First and last column numbers with non-zero cells in a range

[tbrownjcsi]

I'm trying to figure out how to get the column numbers for the first and last non-zero cells in a range. It seems pretty easy to get the actual values using some tips I found on here, but getting the actual column numbers eludes me.

Each cell represents a week, with the first non-zero being the start week and the last non-zero being the end week. All cell values are integers.

[shg]

If rng is a row vector, then

=MATCH(TRUE, INDEX(rng<>0, 0), 0) + COLUMN(rng) - 1 returns the column of the first non-zero value, and

=MATCH(2, INDEX(1/(rng<>0), 0), 1) + COLUMN(rng) - 1 returns the last

[oldchippy]

Here's another option?

If your dates are in the range B1:H1

=SUMPRODUCT(--(B1:H1=MIN(B1:H1))*(COLUMN(B1:H1)))

=SUMPRODUCT(--(B1:H1=MAX(B1:H1))*(COLUMN(B1:H1)))

[shg]

If you're happy using array formulas (must be confirmed with Ctrl+Shift+Enter), you could use

=MIN(IF(rng<>0, COLUMN(rng))) for the first and

=MAX(IF(rng<>0, COLUMN(rng))) for the last