Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
krowlan
 
Posts: n/a
Default 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
  #2   Report Post  
Ron Coderre
 
Posts: n/a
Default 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

  #3   Report Post  
Ron Coderre
 
Posts: n/a
Default 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

  #4   Report Post  
Bernie Deitrick
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert text-format number to number in excel 2000%3f Larry Excel Discussion (Misc queries) 1 July 29th 05 08:18 PM
Hints And Tips For New Posters In The Excel Newsgroups Gary Brown Excel Worksheet Functions 0 April 15th 05 05:47 PM
Using the Find tool in EXCEL TK Excel Worksheet Functions 2 February 11th 05 08:51 PM
How do I find random number in list of random alpha? (Position is. jlahealth-partners Excel Discussion (Misc queries) 0 February 8th 05 06:31 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 12:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"