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 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)
Great idea. And if color from left to right
=E2+(1/COLUMN(E2))%%%>=AGGREGATE(14,6,$E2:$Z2+(1/COLUMN($E2:$Z2))%%%,9)
Can somebody explain me why we add column number divided by 100000 to the cell value?
Hi
This construction creates unique values. No duplicates anymore.
