+ Reply to Thread
Results 1 to 8 of 8

Summing Highest Values based on two columns

  1. #1
    Registered User
    Join Date
    05-08-2012
    Location
    Halifax, Nova Scoita
    MS-Off Ver
    Excel 2007, 2011
    Posts
    2

    Summing Highest Values based on two columns

    Hi all,

    We have spreadsheet that has muliple grades such as ...

    John Smith 500 76
    John Smith 500 62
    Joe Green 1000 42
    Joe Green 1000 76
    Joe Green 1000 58
    John Smith 1000 77

    So what I need is a formula that for each person and category, it will take the two highest values and average them. For exam, John Smith 500 - should average 73.62, Joe Green 1000 - should average 76,58, John Smith 1000 - average should be 77

    does anyone know how to do this using excel formulas

  2. #2
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Summing Highest Values based on two columns

    Can you attach a sample sheet?

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,175

    Re: Summing Highest Values based on two columns

    I am not exactly sure how your table is structure but the sample file should give you a good start

    Enter the formula with Ctrl + Shift + Enter =AVERAGE(LARGE(IF(($A$1:$A$6=F1),$B$1:$B$6),{1,2}))
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Summing Highest Values based on two columns

    If those are in columns A1:C6, and you have unique name/category combos listed starting in D1:E1, then in F1:

    =AVERAGE(LARGE(IF($A$1:$A$6=D1,IF($B$1:$B$6=E1,$C$1:$C$6)),{1,2}))

    confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Summing Highest Values based on two columns

    What is the significance of CTRL+SHIFT+ENTER?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Summing Highest Values based on two columns

    It tells Excel that you have input an Array Formula.

  7. #7
    Registered User
    Join Date
    05-08-2012
    Location
    Halifax, Nova Scoita
    MS-Off Ver
    Excel 2007, 2011
    Posts
    2

    Re: Summing Highest Values based on two columns

    I've attached a sample and on the same sheet demonstrated the values to return in the results.. I'll take a look at the suggestions. Thanks all.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Summing Highest Values based on two columns

    Try in K2:

    =AVERAGE(LARGE(IF($A$2:$A$13=H2,IF($B$2:$B$13=I2,IF($C$2:$C$13=J2,$D$2:$D$13))),{1,2}))

    confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down

    Note: in column A, you have the name Brown with extra space at the end.. they need to be removed.

+ 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