Forum Discussion
EXcel SUMPRODUCT
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)
it selects the first largest number and eliminates the rest so basically if you have 3 of the same highest number it selects the first one
- If you have row of numbers like this:
40,38,32,37,38,38,36,38,38,33,34,37,35,31,41,42
Will it select only the first 38 and and the first 37 ignore the other 38's and 37's
to end up like this as the 9 selected
42,41,40,38,37,36,35,34,32
