- 546K Members
- 2,877 Online
- 652K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- EXcel SUMPRODUCT

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-23-2018 08:51 PM

=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 formula to select the 9 largest numbers in a row

I then use conditional formatting to highlight the selected cells

Can anyone tell me what the formula for 9 selections should look like?

Vic

Labels:

24 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-23-2018 09:40 PM - edited 11-23-2018 09:41 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-23-2018 09:50 PM - edited 11-23-2018 09:51 PM

Hello

For highlighting the top 9 values:

=E2>=AGGREGATE(14,6,$E2:$Z2,9)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2018 04:14 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2018 08:14 AM

Hi,

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

And this is including the duplicates.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-26-2018 06:30 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-26-2018 07:35 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-26-2018 07:51 PM

Hi,

If you want to apply the rule to multiple rows, please select them and apply the rule to them.

But you have to change the conditional formatting formula a little bit as follows:

=MATCH(A2,LARGE($A2:$V2,ROW($A$1:$A$9)),0)

Where **$A2:$V2** is the range of the numbers across all rows.

But please note that you have to use only a single dollar sign before the column reference, this is to lock the columns but not the rows to allow the formula to go down to other rows.

With regards to range **A1:A9**, it must be locked in all directions like this **($A$1:$A$9)**.

Please find the attached file to test this solution.

Hope that helps

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-26-2018 09:44 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2018 06:33 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2018 01:55 PM

=E2+COLUMN(E2)%%%>=AGGREGATE(14,6,$E2:$Z2+COLUMN($E2:$Z2)%%%,9)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-02-2018 06:49 AM

Great idea. And if color from left to right

=E2+(1/COLUMN(E2))%%%>=AGGREGATE(14,6,$E2:$Z2+(1/COLUMN($E2:$Z2))%%%,9)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-02-2018 08:42 AM

Hi Vic,

I'm just wondering if my last reply has solved the issue?

Do you need any further help?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-02-2018 10:29 PM

Can somebody explain me why we add column number divided by 100000 to the cell value?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-03-2018 08:26 AM

Hi

This construction creates unique values. No duplicates anymore.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-03-2018 07:00 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-03-2018 11:35 PM

yes but comparison is done against the largest 9th number. so basically you do not need a unique number.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-03-2018 11:40 PM

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)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 06:35 AM

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)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 08:18 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 11:48 PM

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

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 04:27 AM

no, this is to select only first occurance of the largest number

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 04:32 AM

I am not quite clear on this.

If the largest number was a 40 and there were three of them, would it only select the first one and ignore the other two even if those numbers made up part of the 9 largest?

If the largest number was a 40 and there were three of them, would it only select the first one and ignore the other two even if those numbers made up part of the 9 largest?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 05:12 AM

I have checked the post throughoutly and I am sorry I have passed your earlier information.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2018 07:19 AM

No worries, the formulas I am now using are working fine.

Thanks for your interest

Vic

Thanks for your interest

Vic

Related Conversations

Transformation of values' lenght in a field based on the number of caracters

Tatynout
in
Excel
on
10-11-2019
87
Views

0 Likes

3 Replies

Inserting New Columns of Empty Space between Pre-existing Columns

dta255
in
Excel
on
07-02-2019
59
Views

0 Likes

1 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft