Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUM PART OF A LIST (2000)

    I need to sum the 4 highest numbers from a row of 6 numbers is this possible? i would be grateful for some help <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SUM PART OF A LIST (2000)

    Hi Steven

    You can use the following array formula where A1:A6 represents the cells where your data is held.

    =SUM(LARGE(A1:A6,ROW(INDIRECT("1:4"))))

    You need to enter the formula by pressing Control+Shift+Enter and not just Enter.

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM PART OF A LIST (2000)

    Thanx tony55 that worked perfectly could you give me an explanation of how it works? <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SUM PART OF A LIST (2000)

    =SUM(LARGE(A1:A6,ROW(INDIRECT("1:4"))))

    To see how this array formula works you need to start in the middle and work outwards.

    ROW(INDIRECT("1:4")) - In an array formula this generates a list in memory consisting of the numbers 1,2,3 and 4. The array formula then uses each of these values in turn in the rest of the formula.
    The first run through will calculate LARGE(A1:A6,1) to produce the largest value, 2nd run through will get the 2nd largest value etc to produce a list in memory containing the 4 largest values from your list.
    Finally the SUM part adds the 4 values to give the desired result.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM PART OF A LIST (2000)

    This type of questions is interesting, where there is always room for some ambiguity.

    Let A4:F4 house:

    {20,25,32,25,17,20}

    Let me state up front that, in what follows, I'm not criticizing the way you worded your question... Rather, I'm trying to bring up two issues that might be of interest regarding this type of questions.

    "[S]um the 4 highest numbers" from A1:F1 can be taken in two ways:

    [1] Sum exactly N largest values;
    [2] Sum largest N values, including the ties, if any, of the Nth value.

    Let H4 house N, that is, 4.

    In J4 enter:

    =LARGE(A4:F4,H4)

    To sum exactly N largest (case [1]), the common idiom is (as Tony has also used in his reply)...

    ROW(INDIRECT(M:N))

    as in:

    =SUMPRODUCT(LARGE(A4:F4,ROW(INDIRECT("1:"&H4))))

    Note that H4 houses N while M = 1.

    The result we get wrt the sample is: 102.

    If [1] is intended computation, there is a faster way that does not use volatile INDIRECT...

    =SUMIF(A4:F4,">"&I4)+(H4-COUNTIF(A4:F4,">"&I4))*I4

    Recall that I4 houses =LARGE(A4:F4,H4).

    A temporal profiling shows that this formula is twice as fast as the one with INDIRECT, even if LARGE(A4:F4,H4) is substituted for I4.

    Note that [1] includes just one instance of the Nth largest value. In our sample there are two of them in A4 and in F4.

    [2] wants to include all instances of the Nth largest value. This requires a different formula:

    =SUMIF(A4:F4,">="&I4)

    or, with the contents of I4, directly incorporated:

    =SUMIF(A4:F4,">="&LARGE(A4:F4,H4))

    See the attachment.
    Microsoft MVP - Excel

Posting Permissions

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