Forum Discussion
EXcel SUMPRODUCT
=SUMPRODUCT((AGGREGATE(14,4,E2:Z2,ROW($1:$5)))/5)
I have been using the above formula successfully to select the 5 largest numbers in a row of 35 numbers
There are some 60 rows
I now want to amend the formula to select the 9 largest numbers in a row
I then use conditional formatting to highlight the selected cells
Can anyone tell me what the formula for 9 selections should look like?
Vic
Hi,
You do that by replacing this part:
ROW($1:$5)
With this:
ROW($1:$9)
You can also simplify the formula this way:
=SUMPRODUCT(LARGE(E2:Z2,ROW(A1:A9)))
This formula will create an array of the largest nine numbers, then add them.
With regards to the conditional formatting, you have to use this formula in a new conditional formatting rule:
=MATCH(E2,LARGE($E$2:$Z$2,ROW($A$1:$A$9)),0)
I hope you find this solution helpful
Regards
Hi, thank you for your help, replacing the 5 with 9 works fine. But with the conditional formatting it highlights all of the duplicate numbers, so it appears as if more numbers have been selected.
It needs an additional condition that only allows highlighting of 9 cells
Regards
Vic
Hi,
The rule highlights only the 9 largest number in the row as the screenshot below:
And this is including the duplicates.
Hello
For highlighting the top 9 values:
=E2>=AGGREGATE(14,6,$E2:$Z2,9)
The conditional format formula needs to be able to exclude duplicates over and above the 9 highest
eg:
If we have 12 numbers like this 40,40,40,40,40,40,40,40,40,40,40,40 the format formulae must select only 9 of the highest
Vic
=E2+COLUMN(E2)%%%>=AGGREGATE(14,6,$E2:$Z2+COLUMN($E2:$Z2)%%%,9)
Hi Vic,
I'm just wondering if my last reply has solved the issue?
Do you need any further help?
Hi Hatham,
I had responses from yourself, erol sinan zorlu, Detief Lewin and Sergei Baklan regarding selecting the 9 largest numbers in a row.
The following three formulas worked fine in selecting only the 9 largest;
=AVERAGE(AGGREGATE(14,6,$E2:$AC2,{1,2,3,4,5,6,7,8,9}))
=ROUND(SUMPRODUCT(LARGE(A2:AC2,ROW($A$1:$A$9)))/9,2)
=SUMPRODUCT((AGGREGATE(14,4,E2:AC2,ROW($1:$9)))/9)
However the problem came with the conditional formatting where I only wanted 9 numbers to be highlighted. When the row contained a number of duplicates some formulas included all of them if they were part of the 9 largest.
=E2>=AGGREGATE(14,6,$E2:$Z2,9) this one did not exclude the duplicates
=E2+(1/COLUMN(E2))%%%>=AGGREGATE(14,6,$E2:$Z2+(1/COLUMN($E2:$Z2))%%%,9) This one works fine from Sergei
So I have settled on =AVERAGE(..........for the 9 largest
And =E2+(1/COLUMN(E2))%%%>= ......... for the conditional formatting.
Thank you and your fellow colleagues in the Tech Community for your help, the problem is now solved
Regards
Vic
for conditional formatting you can use below formula also to eliminate duplicates. so if you have more than 1 from that biggest number only the first one will be selected.
=(A2>=AGGREGATE(14;6;$A2:$V2;9))*(COUNTIF($A2:A2;A2)=1)
