Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Six of the best (Excel 2000 SP3)

    In column A of a worksheet I have a name. In columns B to K are entered the results achieved by that name on ten occasions. In column L is the total of the results. However I want to select the six best of the ten results & total them. This is my cumbersome formula to do this:

    =SUM(LARGE(B2:K2,1)+(LARGE(B2:K2,2)+(LARGE(B2:K2,3 )+(LARGE(B2:K2,4)+(LARGE(B2:K2,5)+(LARGE(B2:K2,6)) )))))

    I am sure there is a simpler way to achieve the required result. Perhaps someone could guide me on how this could be done?

    Also & ideally I would like to highlight the six best results but that is a wish rather than a need!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Six of the best (Excel 2000 SP3)

    You can use conditional formatting to highlight the six best results:

    - Select B2:K10 or as far down as needed.
    - Select Format | Conditional Formatting.
    - Select 'Cell Value Is', then 'Greater then or equal to'.
    - In the box next to it, enter

    =SMALL($B2:$K2,4)

    - Click Format...
    - Activate the Patterns tab and select a highlight color.
    - Click OK twice.

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Six of the best (Excel 2000 SP3)

    Thank you Hans - using conditional formatting had not occurred to me!

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Six of the best (Excel 2000 SP3)

    you can shorten up the formula a little...see here

    The formula for your example would be =SUM(LARGE(B2:K2,{1,2,3,4,5,6})) and is an array formula, so confirm with ctl+shift+enter

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Six of the best (Excel 2000 SP3)

    I prefer the array formula:
    <pre>=SUM(LARGE(B2:K2,ROW(INDIRECT("1:6"))))</pre>


    As it does not require entering the numbers 1-6, but allows you to just use the range. It is easier, especially, if you have along list (eg top 25, top 100 etc)...

    Steve

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Six of the best (Excel 2000 SP3)

    Thank you for response & the pointer to OzGrid is appreciated also.

  7. #7
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Six of the best (Excel 2000 SP3)

    My thanks for your reply. Your help is appreciated very much.

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

    Re: Six of the best (Excel 2000 SP3)

    What follows is a faster way of summing stricly 6 largest ...

    =SUMIF(B2:K2,">"&B4)+(6-COUNTIF(B2:K2,">"&B4))*B4

    where B4 houses:

    =LARGE(B2:K2,6)

    than invoking the volatile INDIRECT in an array formula.
    Microsoft MVP - Excel

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Six of the best (Excel 2000 SP3)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Clever...

    Steve

  10. #10
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Six of the best (Excel 2000 SP3)

    Not so clever. Aladin, I included an attachment in which can be seem that the formula does not always work.

    I updated the formula so that you can choose in the yellow block how many "largest" numbers you want to sum.

    In the first sample you can see that everything is working fine.

    In sample 2 and 3 you can see that when you have the same number repeating you start to have problems.

    In sample 3 it add only two occurrences of the number. You would think that since you have only one number repeating that it would add the number 10 times since it is the largest number.

    Regards

    Kobus
    Regards
    Kobus

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Six of the best (Excel 2000 SP3)

    I think it depends on what you want to accomplish. Malcolm Walker (the original poster) wanted to add the six highest results. If the 6th highest result occurs more than once, I guess he still wants to add only 6 results, to be able to compare it to the sum for others. In your 3rd example, where you want to take the highest result, 8 is what you would expect if you want to return one result. So Aladin's formulas do what Malcom asked, in my opinion.
    Of course, there may be other situations in which you want to include duplicates in the sum.

  12. #12
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Six of the best (Excel 2000 SP3)

    Hans

    Thank you. I agree with your argument. As you say it depends on what you want to achieve.

    How could the formula be adjusted to include multiple occurrences of a number?

    Regards
    Regards
    Kobus

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Six of the best (Excel 2000 SP3)

    The formula in B7 would simple be

    <code>=SUMIF(B2:K2,">="&B4)</code>

    and similar in B16 and B24 in your worksheet.

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

    Re: Six of the best (Excel 2000 SP3)

    That's a formula for "Sum exactly/strictly N largest", as Hans also points out. A typical situation for which such a calculation makes sense is:
    Total of N best scored quizzes in order to calculate an average.

    If you want to include the ties of the Nth largest value, see Hans's reply with SumIf.
    Microsoft MVP - Excel

  15. #15
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Six of the best (Excel 2000 SP3)

    Hans & Aladin

    Thank you very much for the lessons.

    Hans, thank you for always being available and ready to help us mere mortals.

    Aladin, I agree with Hans, clever, clever!

    Regards
    Regards
    Kobus

Posting Permissions

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