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...
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