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

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
 
G

Guest

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?
 
G

Guest

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]
 
B

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
 
Joined
Sep 6, 2009
Messages
1
Reaction score
0
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
 
Joined
Feb 14, 2013
Messages
1
Reaction score
0
Could someone tell me what the "--" does in the MATCH part of the above formula:
MATCH(1,--($A$1:$A$2000>0.5)

thanks
 
Joined
Jun 21, 2014
Messages
1
Reaction score
0
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
=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)
 
Joined
May 28, 2015
Messages
1
Reaction score
0
@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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top