|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 27th, 2009, 03:56 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
locate first value greater than
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? 
|
|
#2
February 27th, 2009, 05:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
locate first value greater than
=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?
|
|
#3
February 27th, 2009, 06:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
locate first value greater than
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?
|
|
#4
March 1st, 2009, 05:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
locate first value greater than
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?
|
|
#5
March 1st, 2009, 10:32 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
locate first value greater than
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
|
|
#6
March 2nd, 2009, 12:00 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
locate first value greater than
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?
|
|
#7
April 12th, 2010, 09:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
locate first value greater than
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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
