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

[krowlan]

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

 

[Ron Coderre]

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$2000>0.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$2000>0.5)*$A$1:$A$2000+($A$1:$A$2000<=0.5)*10^99))

Do either of those help?

 

[Ron Coderre]

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

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

 

[Bernie Deitrick]

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

=IF(MAX(A1:A1000)>0.5,INDEX(A:A,MIN(IF(A1:A1000>0.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