# Thread: Six of the best (Excel 2000 SP3)

1. ## 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. ## 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. ## Re: Six of the best (Excel 2000 SP3)

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

4. ## 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. ## 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. ## Re: Six of the best (Excel 2000 SP3)

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

8. ## 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.

9. ## Re: Six of the best (Excel 2000 SP3)

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

Steve

10. ## 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

11. ## 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. ## 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

13. ## 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. ## 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.

15. ## Re: Six of the best (Excel 2000 SP3)

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

#### Posting Permissions

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