+ Reply to Thread
Results 1 to 9 of 9

Finding Max number within a specific range of numbers

  1. #1
    Registered User
    Join Date
    08-13-2014
    Location
    Longview, WA
    MS-Off Ver
    2010
    Posts
    12

    Finding Max number within a specific range of numbers

    I have a file where I track work order numbers. They are assigned a tracking number by area 1XXXX for area 1, 2XXXX for area 2, etc. I have the top of the sheet frozen so I can see the lates number assigned by area but I am currently updating that manually. Is there a way to have excel look at the list of all work orders and display the max between 10000 and 19999?

    Thank you for your help!

  2. #2
    Registered User
    Join Date
    08-13-2014
    Location
    Longview, WA
    MS-Off Ver
    2010
    Posts
    12

    Re: Finding Max number within a specific range of numbers

    *latest (just a correction)

  3. #3
    Registered User
    Join Date
    08-13-2014
    Location
    Longview, WA
    MS-Off Ver
    2010
    Posts
    12

    Re: Finding Max number within a specific range of numbers

    I'm thinking =LARGE(IF( "number from list" <20000),1)

    but i don't know how to input the number from the list as a whole array of numbers. This would be for area 1 by the way ^

  4. #4
    Registered User
    Join Date
    08-13-2014
    Location
    Longview, WA
    MS-Off Ver
    2010
    Posts
    12

    Re: Finding Max number within a specific range of numbers

    Another problem is I wouldn't know what to use for the values if it fails or passes the logic test...

  5. #5
    Registered User
    Join Date
    04-02-2015
    Location
    MO
    MS-Off Ver
    2010
    Posts
    9

    Re: Finding Max number within a specific range of numbers

    See formula below. I used A4:A20 as my sample range of test data, so you will just need to adjust that based on your actual range.

    =MAX(IF(LEFT(A4:A20,1)="1",A4:A20))

    Enter this formula as an array by using Ctrl+Shift+Enter rather than just Enter. It will look like this if you've entered it correctly (notice the extra brackets):

    {=MAX(IF(LEFT(A4:A20,1)="1",A4:A20))}

    This matches the left character from the tracking number from whichever group you want to pull the max number. The formula for the tracking numbers starting with "2" will look like this:

    {=MAX(IF(LEFT(A4:A20,1)="2",A4:A20))}

    Hope this helps.

  6. #6
    Registered User
    Join Date
    08-13-2014
    Location
    Longview, WA
    MS-Off Ver
    2010
    Posts
    12

    Re: Finding Max number within a specific range of numbers

    Thank you, That worked perfectly!

  7. #7
    Registered User
    Join Date
    02-28-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Finding Max number within a specific range of numbers

    Hi can't get the problem. can anyone provide me the sample sheet so that i can understand this please.

    Thanks.
    Perpectuals

  8. #8
    Registered User
    Join Date
    04-02-2015
    Location
    MO
    MS-Off Ver
    2010
    Posts
    9

    Re: Finding Max number within a specific range of numbers

    Attached is the sample workbook that I used.
    Attached Files Attached Files

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,905

    Re: Finding Max number within a specific range of numbers

    If you using Excel 2010 version and up you can take advantage of AGGREGATE function for array formulas that do not require Ctrl+Shift+Enter confirmation.

    =AGGREGATE(14,6,A5:A21/((--LEFT(A5:A21)=1)),1)

    or this formula that will be compatible with excel versions prior to 2010 version.

    =MAX(INDEX((--LEFT(A5:A21)=1)*A5:A21,0))

    ***change =1 in formulas to =2 to get the second MAX value
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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