How to find the first nonzero cell in a row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet that has store names as rows, week numbers as columns, and
inventory quantities as cells. I need a column that has a formula that shows
the first week that has inventory at each store.

Basically, I need to find the the column with the first non-zero cell in
each row, then return the week number listed at the top of that column in the
column header. I need the appropriate weeknumber for each row.

Any ideas would be appreciated!

Thanks

Katie
 
I have a worksheet that has store names as rows, week numbers as columns, and
inventory quantities as cells. I need a column that has a formula that shows
the first week that has inventory at each store.

Basically, I need to find the the column with the first non-zero cell in
each row, then return the week number listed at the top of that column in the
column header. I need the appropriate weeknumber for each row.

Any ideas would be appreciated!

Thanks

Katie
Click to expand...

Assumptions:

1. Your week numbers are in row 1
2. Your weeknumbers and data start in Column D

This **array** formula will return the contents of row1 that is in the same
column as the first non-zero contents of the referenced row (row 3 in this
instance):

=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV3>0,0))

You can copy/drag the formula down as needed.

To enter an array formula, after typing or pasting it in, you must hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula if you did it correctly.


--ron
 
Thanks! That worked perfectly.

Ron Rosenfeld said:
Assumptions:

1. Your week numbers are in row 1
2. Your weeknumbers and data start in Column D

This **array** formula will return the contents of row1 that is in the same
column as the first non-zero contents of the referenced row (row 3 in this
instance):

=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV3>0,0))

You can copy/drag the formula down as needed.

To enter an array formula, after typing or pasting it in, you must hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula if you did it correctly.


--ron
Click to expand...
 
Hi,

I tried the INDEX & MATCH formulas and it works! But if my row # is
dynamic, i.e. take your example below that if row 3 be replaced with
different row #, how to incorporate the dynamic # in MATCH.

Lmm
 
Back
Top