Returning the 5 highest values in a row.

Hello everyone,

I am working on an excel project that will help me calculate overall points based on several competitions. To be more specific, I have a row of 8 values, and would like the SUM of the five highest values in that row.

Thanks in advance

LucG

perhaps...

=SUM(LARGE(A1:H1,{1,2,3,4,5}))

It's rather bulky because Conditional Formatting doesn't accept unions but try

Formula:

Please Login or Register  to view this content.

Aha..

use conditional formatting.
Select the row, and 'under conditional formatting' ---- 'format only top or bottom ranked values' ---'Bottom'---'3'---'format'

Copy and paste special format for below rows too!

Does the attached work for you?

The logic is the least 3(or blanks) are not the top 5!

As Ace said, use conditional formatting (Home Tab>Conditional Formatting> New Rule) and then Aces's suggestion is better there's an option for Bottom 10 (which can be modified to bottom 3 after you click it). (Learn something new everyday)

That will always give the bottom 3 values. I take it, you want only the values that are not used in the Top 5?
What if the numbers are something like 55, 52, 50, 49, 45, 45, 45, 42? One of the 45's is used as top 5 Sum. What would you want highlighted?
This formula in Conditional formatting will highlight anything less than the minimum value in top 5

Formula:

Please Login or Register  to view this content.

You could also use a IF(COUNT(..)>7 function to first look to see how many numbers you have if that affects how you want to color results.

Edit: Attached file

Originally Posted by ChemistB

I take it, you want only the values that are not used in the Top 5?

Yes, that is right!

Originally Posted by ChemistB

What if the numbers are something like 55, 52, 50, 49, 45, 45, 45, 42? One of the 45's is used as top 5 Sum. What would you want highlighted?

In that case I would want it to appear as follows:

55 52 50 49 45 45 45 42


Basically I want the SUM of the five highest values, the values that are not used in the top 5 should be red.

If there are only six values it should read like this. The code must work if the two lowest values are identical.

55 52 50 49 45 45



---------- Post added at 01:03 PM ---------- Previous post was at 01:01 PM ----------

Originally Posted by Ace_XL

Does the attached work for you?

The logic is the least 3(or blanks) are not the top 5!

The whole table is highlighted red for some reason. Excel versions??

Okay, see attached

First rule
=B5<LARGE($B5:$I5,5) This will turn any cell red that is less than the 5th largest value

Second Rule
=IF(B5=LARGE($B5:$I5,5),COUNTIF($B5:$I5,">"&LARGE($B5:$I5,5))+COUNTIF($B5:B5,LARGE($B5:$I5,5))>5)
If the value in the cell is equal to the 5th largest value and the count of the values greater than that plus the count of the values to the left of that cell that are equal to the 5th largest value is greater than 5, turn that cell red.

If the 5th largest value is 0, then it seems to turn the blanks red even when I add a condition to say if it's a blank, keep it clear. :-/ Not sure what to do about that. Take a look at this attachment though. Maybe Ace can figure it out.