Highlight highest 5 values in each of a number of rows

[Vic Curran]

=AVERAGE(LARGE(E2:X2,1),LARGE(E2:X2,2),LARGE(E2:X2,3),LARGE(E2:X2,4),LARGE(E2:X2,5))

I am using the above formula to average the highest 5 numbers in a row of 20 plus numbers that get added to each week. There are some 60 rows.

I am currently update and manually highlight the 5 highest numbers in each row each week as more numbers are added. This is extremely tedious and I have tried various ways using conditional formatting. I can do it for a single row but not for the whole sheet .

How can I get the 5 highest numbers in each row to highlight with a colour automatically each week

Any help would be appreciated

[#2]

Hi Vic,

The formula to average could be like

=AVERAGE(AGGREGATE(14,6,$E2:$X2,{1,2,3,4,5}))

The rule formula to highlight largest 5 is

=E2>=AGGREGATE(14,6,$E2:$X2,5)

with the rule applied to your entire range.

And attached.

[#3]

@Sergei Baklan This mostly works as long as your numbers are unique, however, if your 6th largest number (not included in the average) is the same as your lowest that is included in the average, then it highlights it too so you now have 6 numbers highlighted.

ie. top 5 values:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

ie. when top 6th value is the same as the lowest in top 5:

1

2

16

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

I haven't been able to come up with a way to stop it after it's highlighted the first top 5 cells.

Any ideas?

Thanks!
John

[#4]

@sadlerj 

It depend how you'd like to calculate an average, on unique top numbers or not. Let assume you have numbers

20,20,20,19,19,18,17,16

If not unique, top 5 values will be

20,20,20,19,19,18

we highlight all of them and take average of them.

If count only unique that will be

20,19,18,17,16

with another average and we could highlight only first met from above numbers.

So, depends on goals.

[#5]

@Sergei Baklanthanks for the reply and explanation. I'm actually not looking for an average calculation.

My problem is, i have a series of about 12 races with a persons race results for each race in a single row. The series points are calculated by taking their top 6 results only. I have the formula for the point calculation working, but i also want to highlight the 6 cells that are being used to come up with their totals.

I've set up the table below with only 8 races and we'll take top 4 for an example.

Name	R1	R2	R3	R4	R5	R6	R7	R8	TOTAL
John	15	0	10	14	14	15	14	14	58

From the table above, you can see the first top 4 values should be 15, 15, 14, 14 = 58

However, the highlighted celled (bolded here) are 15, 15,14,14,14,14,10. It's highlighting all instances of any of the top 4 values.

I hope i'm explaining this clearly.

Is there a way without a macro or vba code to do this the way i'm trying to do it?

Thanks again!
John