Six of the best (Excel 2000 SP3)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I prefer the array formula:

=SUM(LARGE(B2:K2,ROW(INDIRECT("1:6"))))

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Clever…

Steve

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

The formula in B7 would simple be

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

and similar in B16 and B24 in your worksheet.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote