Page 1 of 3 123 LastLast
Results 1 to 10 of 23
Like Tree2Likes

how do I find the SECOND highest value in a series?

This is a discussion on how do I find the SECOND highest value in a series? within the Excel Questions forums, part of the Question Forums category; How do I find the 2nd highest value in a series? If i do Max(d4:d11) then I get the max. ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    4

    Default

    How do I find the 2nd highest value in a series? If i do Max(d4:d11) then I get the max. I just want to know how to do the second highest, max minus one, as it were.
    Appreciate the help. I imagine it's an easy query. Thanks.D


    [ This Message was edited by: t_h_e_train on 2002-08-14 16:33 ]

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    Hi - welcome to the board.

    Check out the large function:

    with

    {10;15;20;19}

    in a1:a4,

    =large(a1:a4,2)

    returns 19
    Paddy
    Samer likes this.

  3. #3
    New Member
    Join Date
    Aug 2002
    Posts
    4

    Default

    Thanks, "Large" worked.

    D


    [ This Message was edited by: t_h_e_train on 2002-08-14 16:44 ]

  4. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    Not sure what you mean here? I could understand if you were adding data to the range (in which case it's a simple matter of making the range dynamic). But delete ,e.g., 19 from the range & the formula will return on the next worksheet calculate - no way to get it quicker than that? What do you mean by 'on the fly'.

    Paddy

    EDIT: just saw you'd deleted your question...

    [ This Message was edited by: PaddyD on 2002-08-14 16:46 ]

  5. #5
    New Member
    Join Date
    Oct 2012
    Posts
    8

    Default Re: how do I find the SECOND highest value in a series?

    Small Function works only if the values are in a array, but i need a formula to pick up the 2 smallest value from the list of values located in different cells.
    Eg: I need second smallest value from the values in A1, B3, C4, D7, E1

  6. #6
    New Member
    Join Date
    Oct 2012
    Posts
    8

    Default Re: how do I find the SECOND highest value in a series?

    Can some one help me pleaseee...

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,333

    Default Re: how do I find the SECOND highest value in a series?

    Although that sounds like a very disorganized data layout....

    Try
    =SMALL(CHOOSE({1,2,3,4,5},A1,B3,C4,D7,E1),2)
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  8. #8
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: how do I find the SECOND highest value in a series?

    Quote Originally Posted by Prashanthgangala View Post
    Small Function works only if the values are in a array, but i need a formula to pick up the 2 smallest value from the list of values located in different cells.
    Eg: I need second smallest value from the values in A1, B3, C4, D7, E1
    Like this...

    =SMALL((A1,B3,C4,D7,E1),2)
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  9. #9
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,333

    Default Re: how do I find the SECOND highest value in a series?

    Quote Originally Posted by T. Valko View Post
    Like this...

    =SMALL((A1,B3,C4,D7,E1),2)
    Nice, didn't know you could do that..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  10. #10
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: how do I find the SECOND highest value in a series?

    Quote Originally Posted by jonmo1 View Post
    Nice, didn't know you could do that..
    There's a handful of functions that accept multiple area references.

    Off the top of my head:

    AVERAGE
    COUNT
    COUNTA
    FREQUENCY
    LARGE
    MAX
    MEDIAN
    MIN
    RANK
    SMALL
    SUM

    There's probably a few more.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

Page 1 of 3 123 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