+ Reply to Thread
Results 1 to 5 of 5

How do I add only the highest three numbers in a range of data?

  1. #1
    emac_mommy
    Guest

    How do I add only the highest three numbers in a range of data?

    I am using Excel to record, add and rank scores for a gymnastics meet. We
    have teams of as many as 20 girls, but only the top three scores count
    towards a team score. How can I program a SUM function that adds only the
    highest three scores.

  2. #2
    Biff
    Guest

    How do I add only the highest three numbers in a range of data?

    Hi!

    =SUM(LARGE(A1:A20,{1,2,3}))

    Biff

    >-----Original Message-----
    >I am using Excel to record, add and rank scores for a

    gymnastics meet. We
    >have teams of as many as 20 girls, but only the top three

    scores count
    >towards a team score. How can I program a SUM function

    that adds only the
    >highest three scores.
    >.
    >


  3. #3
    Debra Dalgleish
    Guest

    Re: How do I add only the highest three numbers in a range of data?

    You can use an array formula to total the top scores. For example, with
    scores in cells B2:B100 --

    =SUM(LARGE(B2:B100,ROW(INDIRECT("1:3"))))

    To array enter the formula, hold the Ctrl and Shift keys, then press Enter.

    There's an example here:

    http://www.contextures.com/xlFunctions01.html#Top5

    emac_mommy wrote:
    > I am using Excel to record, add and rank scores for a gymnastics meet. We
    > have teams of as many as 20 girls, but only the top three scores count
    > towards a team score. How can I program a SUM function that adds only the
    > highest three scores.



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  4. #4
    Harlan Grove
    Guest

    Re: How do I add only the highest three numbers in a range of data?

    "Debra Dalgleish" <[email protected]> wrote...
    >You can use an array formula to total the top scores. For example, with
    >scores in cells B2:B100 --
    >
    > =SUM(LARGE(B2:B100,ROW(INDIRECT("1:3"))))
    >
    >To array enter the formula, hold the Ctrl and Shift keys, then press Enter.

    ....

    If the number of values to average is small (subjective, but 3 is definitely
    a small number), and if you're hardcoding the 2nd arg to LARGE anyway
    ("1:3"), why not use

    =SUM(LARGE(B2:B100,{1,2,3}))

    which doesn't have to be entered as an array formula?



  5. #5
    Ragdyer
    Guest

    Re: How do I add only the highest three numbers in a range of data?

    Are you not receiving all posted messages Harlan?

    This is exactly what Biff posted, as a suggestion in his original reply to
    this thread ... 2 days ago!

    That made me also wonder at Debra's not seeing Biff's post?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "Harlan Grove" <[email protected]> wrote in message
    news:%[email protected]...
    > "Debra Dalgleish" <[email protected]> wrote...
    > >You can use an array formula to total the top scores. For example, with
    > >scores in cells B2:B100 --
    > >
    > > =SUM(LARGE(B2:B100,ROW(INDIRECT("1:3"))))
    > >
    > >To array enter the formula, hold the Ctrl and Shift keys, then press

    Enter.
    > ...
    >
    > If the number of values to average is small (subjective, but 3 is

    definitely
    > a small number), and if you're hardcoding the 2nd arg to LARGE anyway
    > ("1:3"), why not use
    >
    > =SUM(LARGE(B2:B100,{1,2,3}))
    >
    > which doesn't have to be entered as an array formula?
    >
    >



+ 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