Home 
Search 
Today's Posts 
#1




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




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




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




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 email 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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ???  Excel Discussion (Misc queries)  
How to format a number in Indian style in Excel?  Excel Discussion (Misc queries)  
convert excel list to pivot table  Excel Discussion (Misc queries)  
Can the number of times undo is used in Excel 2002 be increased?  Setting up and Configuration of Excel  
GET.CELL  Excel Worksheet Functions 