return the first value in a column

B

Ben

What would be the most efficient way of returning the value of the first
NON-ZERO value in a column.

THANK YOU
 
B

Bob Phillips

=INDEX(A1:A20,MIN(IF(A1:A20<>"",ROW(A1:A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

snasui

Ben เขียน:
What would be the most efficient way of returning the value of the first
NON-ZERO value in a column.

THANK YOU
Click to expand...

Try this:

=INDEX(B2:B19,MATCH(1,(B2:B19>0)+0,0))

Ctrl+Shif+Enter
 
S

snasui

Ben เขียน:
What would be the most efficient way of returning the value of the first
NON-ZERO value in a column.

THANK YOU
Click to expand...

If A2:A11 contain your data.

B2

=INDEX(A2:A11,MATCH(1,(A2:A11>0)+0,0))

Ctrl+Shift+Enter

Hope this helps.
 
Top