How to find the first nonzero cell in a row
On Mon, 17 Oct 2005 12:26:04 -0700, "Katie"
wrote:
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
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:IV30,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
|