Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Largest number starting with "9" in a list

This is a discussion on Largest number starting with "9" in a list within the Excel Questions forums, part of the Question Forums category; Hello. I'm wanting a function to display the largest number starting with 7,8 and 9. I have a list of ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    7

    Default Largest number starting with "9" in a list

    Hello.

    I'm wanting a function to display the largest number starting with 7,8 and 9.

    I have a list of 4 digit numbers, beginning 7,8, or 9, and want 3 different cells.

    I need:
    One displaying the largest number in the group starting with 7.
    One displaying the largest number in the group starting with 8.
    And one displaying the largest number in the group starting with 9.

    Could I make use of an IF function and the MAX function?

    Something like:
    M20:
    =IF("1st digit = 7", "display the largest of the list")
    M21:
    =IF("1st digit = 8", "display the largest of the list")
    M22:
    =IF("1st digit = 9", "display the largest of the list")

    I've used =MAX(H5:H75), but this always displays the top "9" number.

    I want something so I can show the top 8xxx number, and the top 7xxx number too.

    Thanks for your help in advance.

    Adam

  2. #2
    Board Regular
    Join Date
    Jul 2008
    Location
    Surrey, UK
    Posts
    1,509

    Default Re: Largest number starting with "9" in a list

    how is your data structured ? thanks

    Kaps
    Read my Excel blog on

    http://simplyspreadsheets.wordpress.com/

    For more ways I can help you with Excel :-

    www.simplyspreadsheets.co.uk

  3. #3

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Largest number starting with "9" in a list

    Hi,

    =MAX(IF(LEFT(H5:H75)+0=9,H5:H75))

    Confirmed with CTRL+SHIFT+ENTER

    HTH

  4. #4
    New Member
    Join Date
    Sep 2008
    Posts
    7

    Default Re: Largest number starting with "9" in a list

    Hi.

    Thanks for your response, but this just gives me #VALUE.

    I am confirming it as a CSE forumula.

    Some of the cells in the column do not have values in them - could this be the problem?

  5. #5
    New Member
    Join Date
    Sep 2008
    Posts
    7

    Default Re: Largest number starting with "9" in a list

    I've got it working with the numbers starting with 9, using:
    =IF((LEFT(MAX(H5:H75))+0=9),MAX(H5:H75))

    Which is just a long winded =MAX(H5:H75).

    How can I get this to work using numbers starting with 7 and 8?

    If I the same formula, just altering the "=9" part, the if statement is always false, because a number beginning with 7 or 8 will never satisfy the first "MAX" statement.

    Thanks for your help.

  6. #6
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118

    Default Re: Largest number starting with "9" in a list

    Are you entering as an array as detailed ?

    The array is maxing only those numbers where left most character = required number... if you don't set the array correctly it won't work.

    To set array enter formula using SHIFT + CTRL + ENTER
    Once set array will appear encased within {}

    Whenever you edit an array formula you must remember to reset it using the above key strokes.
    Does my a$$ look big in this picture ?

  7. #7
    Board Regular
    Join Date
    Sep 2008
    Location
    Brisbane, Australia
    Posts
    767

    Default Re: Largest number starting with "9" in a list

    Quote Originally Posted by adambailey View Post
    Some of the cells in the column do not have values in them - could this be the problem?
    In short, (I think) yes.

    Perhaps if they are always 4 digit numbers, then Ctrl+Shift+Enter

    =MAX(IF(INT(H5:H75/1000)=9,H5:H75))

    which can be adapted for 8 and 7.

  8. #8

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Largest number starting with "9" in a list

    Hi,

    Another way..

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    1
    Numbers Ist digit 7Ist digit 8Ist digit 9
    2
    920FALSEFALSETRUE780888980
    3
    852
    4
    880
    5
    922
    6
    831
    7
    742
    8
    964
    9
    821
    10
    885
    11
    839
    12
    13
    780
    14
    703
    15
    980
    16
    852
    17
    762
    18
    888
    19
    818
    20
    953
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Formula in B2:

    =LEFT(A2)="7"

    In E2:

    =DMAX($A$1:$A$20,1,B1:B2)

    HTH

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,556

    Default Re: Largest number starting with "9" in a list

    Quote Originally Posted by adambailey View Post
    Hi.

    Thanks for your response, but this just gives me #VALUE.

    I am confirming it as a CSE forumula.

    Some of the cells in the column do not have values in them - could this be the problem?
    Change

    =MAX(IF(LEFT(H5:H75)+0=9,H5:H75))

    to

    =MAX(IF(ISNUMBER($H$5:$H$75),IF(LEFT($H$5:$H$75)+0=J20,$H$5:$H$75)))

    where J20 houses 7, J21 8, and J22 9.

    The formula needs still to be confirmed with control+shift+enter.

  10. #10
    New Member
    Join Date
    Sep 2008
    Posts
    7

    Default Re: Largest number starting with "9" in a list

    Quote Originally Posted by AdamL View Post
    In short, (I think) yes.

    Perhaps if they are always 4 digit numbers, then Ctrl+Shift+Enter

    =MAX(IF(INT(H5:H75/1000)=9,H5:H75))

    which can be adapted for 8 and 7.
    That can't be adopted fro 7 or 8, that's the problem with all the suggestions so far.

    It will work for 9xxx numbers, but nothing else.

    The reason is that a 7xxx or 8xxx number will never fullfill the "MAX" requirement of the function.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com