Find the row that has the maximum value across a number of columns

Copper Contributor

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

12 Replies

@JNollett can you please share the file?

@JNollett 

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.

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?

 

@JNollett 

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.

 

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

@JNollett 

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])

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

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

@JNollett 

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.

@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.

@JNollett 

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.

 

@Twifoo 

I think the term

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

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

@Peter Bartholomew 

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.