Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 3rd 05, 05:15 PM
pugsly8422
 
Posts: n/a
Default How do I pick a number from a list in Excel?

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

  #2   Report Post  
Old January 3rd 05, 05:25 PM
Bob Phillips
 
Posts: n/a
Default

Sort the data descending, then assuming the test value is in B1, use

=MATCH(B1+1,A:A,-1)

--
HTH

-------

Bob Phillips
"pugsly8422" wrote in message
...
I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that

is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.



  #3   Report Post  
Old January 3rd 05, 05:59 PM
Peo Sjoblom
 
Posts: n/a
Default

One way,

with the numbers in A1:A10 and the inout cell in B1

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

Regards,

Peo Sjoblom

"pugsly8422" wrote:

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

  #4   Report Post  
Old January 3rd 05, 06:06 PM
Sandy Mann
 
Posts: n/a
Default

Pugsly,

If your data is not always going to be integers then try:

=MIN(IF(A1:A99B1,A1:A99))

This is an array formula so enter ir with Control + Shift + Enter instead of
just Enter

HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"pugsly8422" wrote in message
...
I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that

is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.



  #5   Report Post  
Old January 3rd 05, 07:07 PM
pugsly8422
 
Posts: n/a
Default

Thank you for your help, this is exactly what I needed.

Thanks also for everyone else's help as well, I really appreciate your help
and time.


"Peo Sjoblom" wrote:

One way,

with the numbers in A1:A10 and the inout cell in B1

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

Regards,

Peo Sjoblom

"pugsly8422" wrote:

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.



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
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 09:36 AM
How to format a number in Indian style in Excel? Victor_alb Excel Discussion (Misc queries) 2 December 21st 04 05:21 AM
convert excel list to pivot table GI Excel Discussion (Misc queries) 0 December 6th 04 07:45 PM
Can the number of times undo is used in Excel 2002 be increased? Austrian Hannes Setting up and Configuration of Excel 2 December 6th 04 06:54 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 03:19 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017