Lookup Array Formula
 

A dozen google searches didn't solve this problem, but I know you
can...

I have a row of data (I actually have a lot of rows, and a lot of
columns):

[0,0,0,5,2,0,3]

I need a formula to find the column# of the first cell with <0 data.

I tried various combinations of match, min, max and array formulas to
no success

Thanks in advance.

aldsv


--
aldsv
------------------------------------------------------------------------
aldsv's Profile: http://www.excelforum.com/member.php...o&userid=20494
View this thread: http://www.excelforum.com/showthread...hreadid=479037

Lookup Array Formula
 

"aldsv" wrote in
message ...

A dozen google searches didn't solve this problem, but I know you
can...

I have a row of data (I actually have a lot of rows, and a lot of
columns):

[0,0,0,5,2,0,3]

I need a formula to find the column# of the first cell with <0 data.

Given $K$71:$Q$71 the row containing your data, the following
returns 14 (column N containing 5):

{=MIN(IF(IF($K$71:$Q$71<0,COLUMN($K$71:$Q$71),0) 0,
IF($K$71:$Q$71<0,COLUMN($K$71:$Q$71),0)))}
FormulaArray

Ciao
Bruno

Lookup Array Formula
 

"Bruno Campanini" wrote in message
...

Better:
Given $K$71:$Q$71 the row containing your data, the following
returns 14 (column N containing 5):

{=MIN(IF($K$71:$Q$71<0,COLUMN($K$71:$Q$71),257))}
FormulaArray

Ciao
Bruno

Lookup Array Formula
 

On Tue, 25 Oct 2005 03:33:36 -0500, aldsv
wrote:


A dozen google searches didn't solve this problem, but I know you
can...

I have a row of data (I actually have a lot of rows, and a lot of
columns):

[0,0,0,5,2,0,3]

I need a formula to find the column# of the first cell with <0 data.

I tried various combinations of match, min, max and array formulas to
no success

Thanks in advance.

aldsv

This **array** formula should do what you requi

=MATCH(TRUE,Range<0,0)+COLUMN(Range)-1

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

It also excludes <blank's from the test, so that

0,0,,5,2,0,3 is equivalent to 0,0,0,5,2,0,3

If this is not what you want, please clarify.


--ron

Lookup Array Formula
 

Bingo. Thank you.


--
aldsv
------------------------------------------------------------------------
aldsv's Profile: http://www.excelforum.com/member.php...o&userid=20494
View this thread: http://www.excelforum.com/showthread...hreadid=479037