Forum Discussion
Find the row that has the maximum value across a number of columns
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
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?
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])
JNollett can you please share the file?
