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 SUMPRODUCT works fine but when I input the conditional formatting rule I get the result below
In addition I only want 9 of the highest highlighted. If the numbers are duplicated and therefore more than 9 are highlighted, it must disregard any duplicate over and above the 9,
EG: if the string of numbers were all the same, say 12 numbers all 39 then only 9 of them must be highlighted.
Thks
to eliminate duplicate higher numbers you can create a secondary table where you eliminate the duplicates with 0. and in conditional formatting you can use this table's value in comparison and apply conditionin on the first table. chec the attached file. you can also see the duplicates removed in second table
