+ Reply to Thread
Results 1 to 5 of 5

How do I pick a number from a list in Excel?

  1. #1
    pugsly8422
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: How do I pick a number from a list in Excel?

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

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

    --
    HTH

    -------

    Bob Phillips
    "pugsly8422" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Peo Sjoblom
    Guest

    RE: How do I pick a number from a list in Excel?

    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. #4
    Sandy Mann
    Guest

    Re: How do I pick a number from a list in Excel?

    Pugsly,

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

    =MIN(IF(A1:A99>B1,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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #5
    pugsly8422
    Guest

    RE: How do I pick a number from a list in Excel?

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1