Turn on suggestions

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

Showing results for

- 443K Members
- 12.2K Online
- 532K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Find the row that has the maximum value across a number of columns

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

Showing results for

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Find the row that has the maximum value across a number of columns

Conversation Options

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

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

09-05-2019 12:13 AM

I have a table that has columns which represent week numbers and rows which represent people. The cells have a number in for each week against each person. What formula can I use to find out the person that has the highest total value across a number of weeks? For example, I want to select weeks 1 to 5 to see who scored the most points

Labels:

12 Replies

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

09-05-2019 01:29 AM

@JNollett can you please share the file?

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

09-05-2019 01:43 AM

You may use an array formula (entered with Ctrl+Shift+Enter) similar to B16 of the attached file, which is:

**=LOOKUP(2,1/(MMULT(B$2:J$11,****TRANSPOSE(B$1:J$1^0*(B$1:J$1>=B$13)*(B$1:J$1<=B$14)))=B$15),****A$2:A$11)**

Note that Column K was presented therein for the sole purpose of verifying the correctness of the result obtained from the foregoing formula.

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

09-08-2019 01:13 PM

Hi @Twifoo

I love the formula, but I have a minor problem in that my data is in a table, so the headers are actually headers and not just cells, so the first part of your formula, that equals 42, returns 0 (zero). I tried changing B$1:J$1 to Table2[[#Headers],[1]:[52]], but that doesn't work and returns 0 (zero) too. Any ideas?

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

09-08-2019 02:14 PM

In a table, the numbered weeks will actually be text. You therefore need either convert the 'To' and 'From' values to text or, conversely, convert the header row to values.

My programming style is not the same as @Twifoo 's so I defined a name '**mask**'

**= (VALUE(Table1[#Headers])>=From) * (VALUE(Table1[#Headers])<=To)**

which I use to filter out unwanted columns. My formula to sum across rows

**= MMULT(Table1, TRANSPOSE(mask) )**

gives the '**score**'.

From there, I part company with @Twifoo and, I expect, you because I am using the latest Office 365 version. I simply sorted the names descending by score

**= @ SORTBY( Name, score, -1 )**

and returned the first value only.

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

09-08-2019 05:10 PM

My suggested formula was a sample. Please attach your sample file for the specifics.

Highlighted

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

09-08-2019 10:18 PM

In the attached version for the file, I converted A1:J11 as Table1. Column L and B15 are presented for verification purposes only. Thus, the revised array formula in B16 is:

**=LOOKUP(2,1/(FREQUENCY(0,****1/(1+MMULT(Table1[[1]:[9]],****TRANSPOSE(Table1[[#Headers],[1]:[9]]^0*****(--Table1[[#Headers],[1]:[9]]>=B$13)*****(--Table1[[#Headers],[1]:[9]]<=B$14)))))),****Table1[Name])**

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

09-09-2019 12:31 AM

@TwifooThat worked until I added data from the next month. Take a look.

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

09-09-2019 12:32 AM

@Peter BartholomewI am using the latest version of Office 365 too, but SCORE is not a field in my data.

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

09-09-2019 05:44 AM

I defined the Name '**score**' using Name Manager. The formula goes into the Refers To dialogue box.

**= MMULT(Table1, TRANSPOSE(mask) )**

Whenever '**score**' appears in a formula on the worksheet, Excel will evaluate it as an array formula without requiring Ctrl+Shift+Enter [Office 365 is moving to evaluate all formulas as array formulas by default]. To display

**= score**

on a worksheet may require CSE (depending on the version of Excel) but

**= MAX( score )**

will work correctly irrespective of the version.

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

09-09-2019 03:17 PM

@Peter BartholomewHow does it work in the file that I attached though, as I cannot appear to get it to work. I don't understand the @sortby command either.

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

09-09-2019 04:22 PM

Seems I still had the workbook in memory on my laptop. It also contains an implementation of one of @Twifoo's formulas. One thing I did when creating the table was to exclude the Name and Totals columns, leaving a blank column to separate them. This allows me to use

**VALUE(Table1[#Headers])**

which grows dynamically with extra columns, rather than

**VALUE(Table1[[#Headers],[1]:[9]])**

which would need to be edited to include additional columns.

The SORTBY column is a new function, introduced last September, and gradually being rolled out as part of the Dynamic Array changes.

**= @ SORTBY( Name, score, -1 )**

sorts the 'Name' array by 'score' in descending order. The '@' sign before the an array result truncates the result to a single value (otherwise I would get a sorted version of the entire list of names).

At present, you will have the new function if you are signed up to Office 365 insider (weekly updates); it is in the process of being rolled out to 'monthly' and 'semi-annual' may have some time to wait.

I think the term

**(Table1[[#Headers],[1]:[9]])****^0**

is redundant since the following terms will give the same result without it.

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

09-09-2019 09:56 PM

I agree. The two conditional clauses cause such redundancy. If there were only one conditional clause, then no such redundancy would have occurred.

Thanks for your keen observation.

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
33.5K
Views

6 Likes

35 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
13K
Views

2 Likes

9 Replies

The announcement regarding self-service purchase capabilities for Power Platform products??

Kelly E
in
Office 365
on
10-21-2019
8,892
Views

17 Likes

52 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
12.6K
Views

9 Likes

9 Replies

Share

Popular

Learning Resources

Programs

Values

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