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