Forum Discussion
EXcel SUMPRODUCT
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)
Erol, your formula selects only one cell instead of 9 cells.
Perhaps you meant:
=(A2<=AGGREGATE(14,6,$A2:$V2,9))*(COUNTIF($A2:A2,A2)<=9)
