Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning the smallest value above a certain criteria
Hi - I have the following array of data, where the top row is the year worked
and the following rows are number of hours worked by employees in that year. In the last column, I would like to return either a) the first year in which the employee worked more than 999.99 hours or b) if the first is too complicated, the number of hours that first exceed 999.99 (and from there I'll do a look up to figure out the year). In the example, I would like to return either a) "2001" as the year the employee first worked over 1,000, or b) 1002 hours (first time that a number over 999.99 appears in the array). I am stumped, although I suspect the solution isn't that complicated. Thanks for your suggestions 2000 2001 2002 2003 2004 2005 26 1002 999 1500 1001 990 90 600 800 14 10 1600 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning the smallest value above a certain criteria
Hi,
Assuming your data starts in A1 with titles on row 1 then in G2 enter the formula: =INDEX(A$1:G$1,1,MIN(IF(COLUMN(A2:F2)*(A2:F2999) 0,COLUMN(A2:F2)*(A2:F2999),""))) as an array, that means press Shift+Ctrl+Enter to enter the formula not Enter. -- Thanks, Shane Devenshire "Andrea K" wrote: Hi - I have the following array of data, where the top row is the year worked and the following rows are number of hours worked by employees in that year. In the last column, I would like to return either a) the first year in which the employee worked more than 999.99 hours or b) if the first is too complicated, the number of hours that first exceed 999.99 (and from there I'll do a look up to figure out the year). In the example, I would like to return either a) "2001" as the year the employee first worked over 1,000, or b) 1002 hours (first time that a number over 999.99 appears in the array). I am stumped, although I suspect the solution isn't that complicated. Thanks for your suggestions 2000 2001 2002 2003 2004 2005 26 1002 999 1500 1001 990 90 600 800 14 10 1600 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning the smallest value above a certain criteria
Sorry,
change the 999 in my formula to 999.99 -- Thanks, Shane Devenshire "Andrea K" wrote: Hi - I have the following array of data, where the top row is the year worked and the following rows are number of hours worked by employees in that year. In the last column, I would like to return either a) the first year in which the employee worked more than 999.99 hours or b) if the first is too complicated, the number of hours that first exceed 999.99 (and from there I'll do a look up to figure out the year). In the example, I would like to return either a) "2001" as the year the employee first worked over 1,000, or b) 1002 hours (first time that a number over 999.99 appears in the array). I am stumped, although I suspect the solution isn't that complicated. Thanks for your suggestions 2000 2001 2002 2003 2004 2005 26 1002 999 1500 1001 990 90 600 800 14 10 1600 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning the smallest value above a certain criteria
try this
suppose the hours are in row 2 =LARGE(A2:F2,COUNTIF(A2:F2,"999.99")) On Sep 28, 10:01*pm, Andrea K <Andrea wrote: Hi - I have the following array of data, where the top row is the year worked and the following rows are number of hours worked by employees in that year. * In the last column, I would like to return either a) the first year in which the employee worked more than 999.99 hours or b) if the first is too complicated, the number of hours that first exceed 999.99 (and from there I'll do a look up to figure out the year). *In the example, I would like to return either a) "2001" as the year the employee first worked over 1,000, or b) 1002 hours (first time that a number over 999.99 appears in the array).. *I am stumped, although I suspect the solution isn't that complicated. Thanks for your suggestions 2000 * *2001 * *2002 * *2003 * *2004 * *2005 26 * * *1002 * *999 * * 1500 * *1001 * *990 90 * * *600 * * 800 * * 14 * * *10 * * *1600 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning cell value if 2 criteria are met | New Users to Excel | |||
MULTIPLE CRITERIA RETURNING SUM OF RESULTS | Excel Worksheet Functions | |||
MULTIPLE CRITERIA RETURNING SUM OF RESULTS | Excel Worksheet Functions | |||
returning blank cell in criteria o | Excel Discussion (Misc queries) | |||
returning a value based on mulitple criteria | Excel Worksheet Functions |