I intend to locate a cell that records the first value which is not zero (an
example is given below). The Index and Match functions don't seem to work.
Any solutions would be highly appreciated.

01/06/05 01/12/05 01/06/06 01/12/06 01/06/07
0% 0% 5% 40% 0%

I intend to capture the cell with date "01/06/06", which shows 5% - i.e. the
first cell from the second row which is not zero.

=INDEX(1:1,MIN(IF(2:2<0,COLUMN(2:2))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jiang" wrote in message
...
I intend to locate a cell that records the first value which is not zero
(an
example is given below). The Index and Match functions don't seem to work.
Any solutions would be highly appreciated.

01/06/05 01/12/05 01/06/06 01/12/06 01/06/07
0% 0% 5% 40% 0%

I intend to capture the cell with date "01/06/06", which shows 5% - i.e.
the
first cell from the second row which is not zero.

One try ..

Assuming data posted is within cols B to F, with the dates in row1,
percentages data running in row2 down

Place in say, K2's formula bar, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX($B$1:$F$1,MATCH(TRUE,$B2:$F20,0))
K2 will return the required date in row1
Copy K2 down to return correspondingly for the other data rows
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jiang" wrote:
I intend to locate a cell that records the first value which is not zero (an
example is given below). The Index and Match functions don't seem to work.
Any solutions would be highly appreciated.

01/06/05 01/12/05 01/06/06 01/12/06 01/06/07
0% 0% 5% 40% 0%

I intend to capture the cell with date "01/06/06", which shows 5% - i.e. the
first cell from the second row which is not zero.

Many thanks for your replies, Max and Bob. that's very helpful indeed

You both suggested a command of CTRL+SHIFT+ENTER, may i ask under what
circumstances i should use this?

Also, Bob I do not seem to understand the formula, what does the 1:1 or 2:2
indicate in the formula - a mystery for me! :)

"jiang" wrote:

I intend to locate a cell that records the first value which is not zero (an
example is given below). The Index and Match functions don't seem to work.
Any solutions would be highly appreciated.

01/06/05 01/12/05 01/06/06 01/12/06 01/06/07
0% 0% 5% 40% 0%

I intend to capture the cell with date "01/06/06", which shows 5% - i.e. the
first cell from the second row which is not zero.

You use Ctrl-Shift-Enter on any formula with a function that wants to work
upon an array of values, but that function 'normally' accepts a single cell
(such as IF in my example). This allows each value in the array to be
processed.

The 1:1 and 2:2 simply refers to row 1 and row 2. It finds the index of
non-zero in row 2 and then looks up the corresponding value in row 1.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jiang" wrote in message
...
Many thanks for your replies, Max and Bob. that's very helpful indeed

You both suggested a command of CTRL+SHIFT+ENTER, may i ask under what
circumstances i should use this?

Also, Bob I do not seem to understand the formula, what does the 1:1 or
2:2
indicate in the formula - a mystery for me! :)

"jiang" wrote:

I intend to locate a cell that records the first value which is not zero
(an
example is given below). The Index and Match functions don't seem to
work.
Any solutions would be highly appreciated.

01/06/05 01/12/05 01/06/06 01/12/06 01/06/07
0% 0% 5% 40% 0%

I intend to capture the cell with date "01/06/06", which shows 5% - i.e.
the
first cell from the second row which is not zero.

Thanks for your explanation on the Ctrl-Shift-Enter function.

Your reply has intrigued me into more questions -

1) if 1:1 etc. refers to a row, is there a function to refer to a column in
general?

2) i can't locate the explanation of "Column" in Excel Help (only
"Columns"-are there any difference in these two functions?). If it means "to
find the column no. which satisfies certain conditions", then the
IF(2:2<0,COLUMN(2:2)) should give a result of "3", but instead it returns a
value of "false".

Many thanks for your help.

"Bob Phillips" wrote:

You use Ctrl-Shift-Enter on any formula with a function that wants to work
upon an array of values, but that function 'normally' accepts a single cell
(such as IF in my example). This allows each value in the array to be
processed.

The 1:1 and 2:2 simply refers to row 1 and row 2. It finds the index of
non-zero in row 2 and then looks up the corresponding value in row 1.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jiang" wrote in message
...
Many thanks for your replies, Max and Bob. that's very helpful indeed

You both suggested a command of CTRL+SHIFT+ENTER, may i ask under what
circumstances i should use this?

Also, Bob I do not seem to understand the formula, what does the 1:1 or
2:2
indicate in the formula - a mystery for me! :)

"jiang" wrote:

I intend to locate a cell that records the first value which is not zero
(an
example is given below). The Index and Match functions don't seem to
work.
Any solutions would be highly appreciated.

01/06/05 01/12/05 01/06/06 01/12/06 01/06/07
0% 0% 5% 40% 0%

I intend to capture the cell with date "01/06/06", which shows 5% - i.e.
the
first cell from the second row which is not zero.

"jiang" wrote in message
...
Thanks for your explanation on the Ctrl-Shift-Enter function.

Your reply has intrigued me into more questions -

1) if 1:1 etc. refers to a row, is there a function to refer to a column
in
general?


But of course <g

A:A, B:B, etc.


2) i can't locate the explanation of "Column" in Excel Help (only
"Columns"-are there any difference in these two functions?).


Really, I find the COLUMN worksheetfunction in Excel help very easily.

Columns is a VBA range property (I fear I am leading you down a path that
goes on for ever <vbg).


If it means "to
find the column no. which satisfies certain conditions", then the
IF(2:2<0,COLUMN(2:2)) should give a result of "3", but instead it returns
a
value of "false".


Don't forget that IF is a single value function, if you use an array in that
function it has to be array-entered (Ctrl-Shift-Enter). If you do that with
your formula, you will not get #VALUE!. You won't get a meaningful answer in
this case, as it returns an array of values, and it will just show the first
of that array. This type of formula would be used in another function that
works upon an array, such as INDEX in my previous example.