Forum Discussion

Vicphuket's avatar
Vicphuket
Copper Contributor
Nov 24, 2018

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...
excel
Formulas and Functions
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Nov 24, 2018

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

  • Vicphuket's avatar
    Vicphuket
    Copper Contributor
    Nov 25, 2018

    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

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Nov 25, 2018

      Hi,

       

      The rule highlights only the 9 largest number in the row as the screenshot below:

       

      And this is including the duplicates.

      • Vicphuket's avatar
        Vicphuket
        Copper Contributor
        Nov 27, 2018

        The SUMPRODUCT works fine but when I input the conditional formatting rule I get the result below

Resources