Find the day which had the most time spent in excel-CodePudding

I have a spreadsheet which looks like this,

Sunday,      Monday,      Tuesday,     Wednesday,   Thursday, Friday, Saturday, Sunday, Monday, Tuesday, ...
13-Nov-2022, 14-Nov-2022, 15-Nov-2022, 16-Nov-2022, 17-Nov-2022, ...
1:00,        2:05,        0:30,        2:00,        1:00,     0:05,   0:10,     1:20, 0:14

I want to find the day which has the maximum sum, that is here, Sunday has 2:20 so I want a column where I mention Sunday. Currently, I have done something like this,

MAX(SUMIFS(range_of_time_spent, range_of_days, a_particular_day), 
    SUMIFS(range_of_time_spent, range_of_days, a_particular_day),
    ...) # seven SUMIFS inside MAX for 7 different days

but this gives me 2:20, not the day

CodePudding user response：

Use:

=TEXT(INDEX({1,2,3,4,5,6,7},MATCH(MAX(SUMIFS(range_of_time_spent,range_of_days,TEXT({1,2,3,4,5,6,7},"dddd"))),SUMIFS(range_of_time_spent,range_of_days,TEXT({1,2,3,4,5,6,7},"dddd")),0)),"dddd")

With older versions this may require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode.

CodePudding user response：

You can try this approach that uses MMULT. In cell A6 put the following formula:

=INDEX(TEXT({1;2;3;4;5;6;7}, "dddd"),
  MATCH(MAX(MMULT(IF(A1:O1=TEXT({1;2;3;4;5;6;7}, "dddd"), A3:O3,0),
   TRANSPOSE(COLUMN(A1:O1)/COLUMN(A1:O1)))),
    MMULT(IF(A1:O1=TEXT({1;2;3;4;5;6;7}, "dddd"), A3:O3,0),
     TRANSPOSE(COLUMN(A1:O1)/COLUMN(A1:O1))),0))

Notes:

MMULT requires as input arguments arrays, it means you can use either ranges or arrays, so it gives more flexibility in case you are forced to use arrays. Let's say instead of calculating the totals of A3:O3, you need to perform an operation first in the range, for example: LOG10(A3:O3) (now it is not a range but an array) or any other operation. In this case you cannot use any of the

Since you requested a solution for an older version, the above formula is more verbose. Let's use LET function (Excel 2021 version) to avoid repetition of the same calculation defining names. It also facilitates the comprehension of the formula (You can use named ranges for some of the range too)
```
=LET(wkdays, TEXT({1;2;3;4;5;6;7}, "dddd"), days, A1:O1, times, A3:O3,
 matrix, IF(days=wkdays, times,0),cols, COLUMN(days), 
 ones, TRANSPOSE(cols/cols),
 totals, MMULT(matrix, ones),
 INDEX(wkdays, XMATCH(MAX(totals), totals))
)
```
The main idea is to generate the matrix so we can perform the calculation in the way we want it. For sake of simplicity let's explain it with the following simple example of two arrays. The following expression:
```
IF({1,2,3,1,2,3}={1;2;3},1,0)
```
will produce the following matrix.
```
1   0   0   1   0   0
0   1   0   0   1   0
0   0   1   0   0   1
```
Note: The shape of the matrix is the number of rows of the right hand side of the IF-comparison and the columns of the corresponding left hand side.

It concatenates a unit matrix (MUNIT(dimension)) per every sequence of values of the first array. Please notice to obtain the desired result, the first array must be a column array and the second a row array. If we want to sum the total number of 1's per row we can do a matrix multiplication as follow: MMULT(matrix, {1;1;1;1;1;1}).

If we want to sum different values than 1's, we just need to specify a different output in case of match, with the same shape as the left hand side of the IF-comparison.

For example:
```
IF({1,2,3,1,2,3}={1;2;3},{10,20,30,40,50,50},0)
```
Understanding the previous logic for this simple case, we can build the same logic for our case.
- wkdays has the weekdays in a row array.
- ones is the unit row array with the same number of rows as columns has days.
- totals is the matrix multiplication: MMULT(matrix, ones) returns the total per wkdays. The result is an array with the same shape as wkdays.
The rest is just to find the index position of the maximum of totals via MATCH/XMATCH and finally use INDEX to find the corresponding weekday.