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

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

Returning the smallest value above a certain criteria
 

Try one of these:

Array entered**:

=INDEX(A$1:F$1,MATCH(TRUE,A2:F2999.99,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Normally entered:

=INDEX(A$1:F$1,MATCH(TRUE,INDEX(A2:F2999.99,0),0) )


--
Biff
Microsoft Excel MVP


"Andrea K" <Andrea wrote in message
...
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

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

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