Sep 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
Sep 05 2019 01:29 AM
@JNollett can you please share the file?
Sep 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.
Sep 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?
Sep 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.
Sep 08 2019 05:10 PM
Sep 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])
Sep 09 2019 12:31 AM
@TwifooThat worked until I added data from the next month. Take a look.
Sep 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.
Sep 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.
Sep 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.
Sep 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.
Sep 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.