How can I get Excel to find the first number in a list greater tha
 

I have a long list of numbers and I would like Excel to report to me the
first number in the list (and not any other) that is greater than a specific
value, say 0.5. Which formula will help me

How can I get Excel to find the first number in a list greater tha
 

I came up with 2 approaches, depending on what you actually require.

If you need the first sequential value in the list that is greater than your
criteria, this may work for you:
=INDEX($A$1:$A$2000,SUMPRODUCT(MATCH(1,--($A$1:$A$20000.5),0)),1)

If you need the smallest value in the entire list that is greater your
criteria, try this:
=SUMPRODUCT(MIN(($A$1:$A$20000.5)*$A$1:$A$2000+($ A$1:$A$2000<=0.5)*10^99))

Do either of those help?

--
Regards,
Ron


"krowlan" wrote:

I have a long list of numbers and I would like Excel to report to me the
first number in the list (and not any other) that is greater than a specific
value, say 0.5. Which formula will help me

How can I get Excel to find the first number in a list greater
 

A shorter version to find the smallest value in the list that is greater than
your critriea is:
=MIN(IF(($A$1:$A$20000.5),$A$1:$A$2000,10^99))

Note: to commit that array formula, hold down the [Ctrl] and [Shift] keys
when you press [Enter]

--
Regards,
Ron


"Ron Coderre" wrote:

I came up with 2 approaches, depending on what you actually require.

If you need the first sequential value in the list that is greater than your
criteria, this may work for you:
=INDEX($A$1:$A$2000,SUMPRODUCT(MATCH(1,--($A$1:$A$20000.5),0)),1)

If you need the smallest value in the entire list that is greater your
criteria, try this:
=SUMPRODUCT(MIN(($A$1:$A$20000.5)*$A$1:$A$2000+($ A$1:$A$2000<=0.5)*10^99))

Do either of those help?

--
Regards,
Ron


"krowlan" wrote:

I have a long list of numbers and I would like Excel to report to me the
first number in the list (and not any other) that is greater than a specific
value, say 0.5. Which formula will help me

How can I get Excel to find the first number in a list greater tha
 

Array enter (enter using Ctrl-Shift-Enter) the formula

=IF(MAX(A1:A1000)0.5,INDEX(A:A,MIN(IF(A1:A10000. 5,ROW(A1:A1000)))),"None are greater than .5")

to find the first number in the range a1:a1000 greater than .5 The .5 can also be a cell reference.

HTH,
Bernie
MS Excel MVP


"krowlan" wrote in message
...
I have a long list of numbers and I would like Excel to report to me the
first number in the list (and not any other) that is greater than a specific
value, say 0.5. Which formula will help me