I would like to be able to return the first column that has a number greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?

The UDF called with, fro example, =Tryme(B2:Q2) will return 3/16
best wishes

Function tryme(myrange)
mylast = myrange.Count
tryme = "No non-zero"
For Each mycell In myrange
If mycell.Value < 0 Then
tryme = mycell.Offset(-1, 0)
Exit For
End If
Next
End Function

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"MPI Planner" wrote in message
...
I would like to be able to return the first column that has a number
greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?

=MIN(IF(B2:F20,COLUMN(B2:F2),FALSE))
Input this as an array (Use Ctrl+Shift+Enter). If done right, you should see
curly brackets {} appear around your formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MPI Planner" wrote:

I would like to be able to return the first column that has a number greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?

Another one:

=INDEX(B1:F1,MATCH(TRUE,INDEX(B2:F20,0),0))

Format as Date

--
Biff
Microsoft Excel MVP


"MPI Planner" wrote:

I would like to be able to return the first column that has a number greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?

This worked great. How can I add a lookup to this? I would like to be able to
reference a cell and find that value in the table to return the same data.

vlookup(z1,a1:y1000,INDEX(B1:F1,MATCH(TRUE,INDEX(B 2:F20,0),0)))?

"T. Valko" wrote:

Another one:

=INDEX(B1:F1,MATCH(TRUE,INDEX(B2:F20,0),0))

Format as Date

--
Biff
Microsoft Excel MVP


"MPI Planner" wrote:

I would like to be able to return the first column that has a number greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?

On Fri, 27 Feb 2009 07:56:03 -0800, MPI Planner
wrote:

I would like to be able to return the first column that has a number greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?


With dates in row 1, and data in row 2:

=MATCH(1,INDEX(ISNUMBER(2:2)*(2:20),0),0) -- 4

=INDEX(1:1,,MATCH(1,INDEX(ISNUMBER(2:2)*(2:20),0) ,0)) -- 3/16


--ron

Hi,

Try this array formula (Ctrl+Shift+Enter)

=INDEX(C7:H8,1,MATCH(TRUE,D8:H80,0)+1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MPI Planner" wrote in message
...
I would like to be able to return the first column that has a number
greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?