Forum Discussion
Highlight highest 5 values in each of a number of rows
=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
4 Replies
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.
SergeiBaklan 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!
JohnIt 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.
