Jul 25 2018 06:13 AM
Jul 25 2018 06:13 AM
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
Jul 25 2018 06:49 AM
The formula to average could be like
The rule formula to highlight largest 5 is
with the rule applied to your entire range.
Sep 18 2022 09:25 AM
@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:
ie. when top 6th value is the same as the lowest in top 5:
I haven't been able to come up with a way to stop it after it's highlighted the first top 5 cells.
Sep 19 2022 01:22 PM
It depend how you'd like to calculate an average, on unique top numbers or not. Let assume you have numbers
If not unique, top 5 values will be
we highlight all of them and take average of them.
If count only unique that will be
with another average and we could highlight only first met from above numbers.
So, depends on goals.
Sep 29 2022 05:36 AM - edited Sep 29 2022 06:00 AM
@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.
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?