+ Reply to Thread
Results 1 to 5 of 5

Finding the next highest number From Input value

  1. #1
    Registered User
    Join Date
    05-01-2013
    Location
    leeds
    MS-Off Ver
    Excel 2010
    Posts
    3

    Finding the next highest number From Input value

    http://www.filedropper.com/simplespreasheet

    I have a spreadsheet that im using to find part numbers based on an input of size.

    I have added a screen shot of a simplified version of what i am using.

    The premise is that you enter the size (In this case 13)
    And below it returns the part number (in this case 12349)

    What i need help with is adding a formula to the 2 boxes below, so that it can return the SIZE (NOT PART NUMBER) for the next size up and the next size down (In this case 8 & 16)

    There will only be 1 of each size and the sizes will never be doubled (In this example i typed in a size twice by error but this doesnt happen on the proper spreadsheet)

    Is there a formula i can used based on the input of size (5) to find the next size up and the next size down?

    Thank you for your help.
    Last edited by bdsturgess; 05-06-2013 at 06:23 PM.

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Finding the next highest number From Input value

    Hi -

    Images aren't very helpful as we just have to type the information into a spreadsheet to experiment with solutions. If you could upload a sample of your spreadsheet (with any sensitive information removed) or your example spreadsheet, it would be greatly appreciated.

    You could use the INDEX function with a MATCH function to locate the row that matches the part number. I don't fully understand the size up and size down. Are the sizes listed in millimeters? If so, that makes finding the next size up and down pretty difficult unless we can sort the whole list by size. Is that an option?
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Finding the next highest number From Input value

    Here's an option using Arrayed formulas (need to enter with CNTRL SHFT ENTER instead of ENTER. You'll see {} around the formula if done right)

    For next size down (with size in F2
    =LARGE(IF($C$2:$C$10<F2,$C$2:$C$10,0),1)

    For next size up
    =SMALL(IF($C$2:$C$10>F2,$C$2:$C$10,10000),1)
    Then use Index/Match to match up the part numbers and descriptions
    i.e =INDEX($A$2:$A$10, MATCH(F3,$C$2:$C$10,0))
    See attachment
    Attached Files Attached Files
    Last edited by ChemistB; 05-07-2013 at 08:24 AM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    05-01-2013
    Location
    leeds
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Finding the next highest number From Input value

    Simple Spreasheet.xlsx
    Hi thanks for the reply.

    This is a link to the spreadsheet i posted in the first post.
    The sizes in the actual spreadsheet are in mm yes...

    The sizes cannot be sorted either unfortunately into an order
    I use the index match function to get the part number from the size based on the table... what i cant get my head around is a formula to tell me what the next size up and next size down is...

  5. #5
    Registered User
    Join Date
    05-01-2013
    Location
    leeds
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Finding the next highest number From Input value

    LOGINJMOR

    CHEMISTB

    Thank you for your help! Thats perfect for what im after!

    Appreciate the help!

+ 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