Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default First occurance greater than or equal to a specified value

Hey-

I have a list of unsorted data and I want to proceed from the top of a
known column to the bottom and return the first number that is greater
than equal to a specified value.

DMIN doesn't work because I want the first occurance that is greater
than the specified value, not necessarily the smallest number.

Thanks.
-Rob

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default First occurance greater than or equal to a specified value

Do you really mean first value greater than or equal that means if you want
to find 12.5 and the second value is 1000
then it will return 1000? Or do you mean find the values that is equal to or
closest larger value? If the latter use

=INDEX(A1:A30,MATCH(SMALL(A1:A30,COUNTIF(A1:A30,"< "&D1)+1),A1:A30,0))

where A1:A30 is the range you want to lookup and D1 holds the lookup value


if 12.5 does not exist it will find the closest larger value for example 13
and not 12.3
If the former

=INDEX(A1:A30,MATCH(TRUE,A1:A30=D1,0))

entered with ctrl + shift & enter

will find the first value that is greater than or equal to 12.5



--
Regards,

Peo Sjoblom

Portland, Oregon




wrote in message
oups.com...
Hey-

I have a list of unsorted data and I want to proceed from the top of a
known column to the bottom and return the first number that is greater
than equal to a specified value.

DMIN doesn't work because I want the first occurance that is greater
than the specified value, not necessarily the smallest number.

Thanks.
-Rob


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default First occurance greater than or equal to a specified value

Assuming that A1:A10 contains your data, and B1 contains the 'specified
value', try...

=INDEX(A1:A10,MATCH(TRUE,A1:A10=B1,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article .com,
wrote:

Hey-

I have a list of unsorted data and I want to proceed from the top of a
known column to the bottom and return the first number that is greater
than equal to a specified value.

DMIN doesn't work because I want the first occurance that is greater
than the specified value, not necessarily the smallest number.

Thanks.
-Rob

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default First occurance greater than or equal to a specified value

That worked! Thanks both of you!

-Rob

Domenic wrote:
Assuming that A1:A10 contains your data, and B1 contains the 'specified
value', try...

=INDEX(A1:A10,MATCH(TRUE,A1:A10=B1,0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article .com,
wrote:

Hey-

I have a list of unsorted data and I want to proceed from the top of a
known column to the bottom and return the first number that is greater
than equal to a specified value.

DMIN doesn't work because I want the first occurance that is greater
than the specified value, not necessarily the smallest number.

Thanks.
-Rob


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
Matching Values from an Array Mal Excel Worksheet Functions 2 January 4th 06 11:06 AM
Formula which is greater tahn or equal to zero bruce2444 Excel Worksheet Functions 2 September 7th 05 12:24 PM
Vlookup but also equal to and greater than? dazman Excel Worksheet Functions 1 August 7th 05 05:59 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM
Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 7th 04 11:50 PM


All times are GMT +1. The time now is 05:31 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"