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

  • Thread starter Thread starter Guest
  • Start date Start date
Status
Not open for further replies.
G

Guest

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
 
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?
 
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]
 
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
 
Return GT values on a different sheet

Hi All

I need the same thing but need to look up info on a different sheet.
On sheet 1 the cell D2 contains the lookup value (0.5 in the previous example), and G2 contains the array.
The values in D2 and G2 contain the full address (sheet, row, column)

How to modify the formula on sheet 1
=INDEX($A$1:$A$2000,SUMPRODUCT(MATCH(1,--($A$1:$A$2000>0.5),0)),1)
so that it references D2 and G2, thus returning the values on sheet 2.

Something to do with the indirect function I believe but I can not seem to make it happen.

Cheers

Sam
 
Could someone tell me what the "--" does in the MATCH part of the above formula:
MATCH(1,--($A$1:$A$2000>0.5)

thanks
 
=?Utf-8?B?Um9uIENvZGVycmU=?= said:
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?

--
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
Click to expand...
=INDEX($A$1:$A$2000,SUMPRODUCT(MATCH(1,--why we use -- in formula, what is the function(--) in this formula ($A$1:$A$2000>0.5),0)),1)
 
@fido, @yasirhabib,

The double negative sign converts TRUE to 1 and FALSE to 0, in this case allowing MATCH to compare "1" to the boolean (TRUE / FALSE) result of the A>B term.

It is also commonly used to convert boolean operands to numeric for use in SUMPRODUCT, which is performing a multiplication and thus needs numeric arguments.

Use of the double negative sign (--):
http://www.k2e.com/tech-update/tips/143-using-two-minus-signs-in-excel
 
Last edited:
Status
Not open for further replies.
Back
Top