Return averageifs of durations formatted to display only minutes

Question

I have a column of durations formatted as "hh:mm:ss".

I use AVERAGEIFS to return the average of the durations that also match a particular date.

For example, I have the following formula in E2:

=AVERAGEIFS(
   $B$2:$B$12, $A$2:$A$12, D3)

The formula works but I want the result displayed in minutes only.

I tried using the HOUR function but it requires 3 arguments.

Can anyone help to convert the formula results in E2:E3 (highlighted in yellow in the image) into minutes without removing the AVERAGEIFS formula?

Answer 1

Basic Formula in E2

Ensure E2:E is formatted as number, not duration

=1440 * AVERAGEIFS(
   B$2:B, A$2:A, D2) 

Multi-Row Formula in E2

=ARRAYFORMULA(
   IF(LEN(D2:D),
     1440 * AVERAGEIFS(
       B2:B, A2:A, D2:D)),)) 

Explanation

Basic Formula

1. The result of your AVERAGEIFS function in E2 is the duration 0:06:15.
2. Sheets stores durations as decimal numbers equal to the equivalent fraction of a day (max 15 digits of precision).
   
   For example, 0:06:15 is stored as 0.00434027777777778 because 6.25 minutes is equivalent to 0.00434 days.
3. To change the stored number to fractions of minutes, instead of fractions of days, simply multiply the number by 14,400, the number of minutes in a day.
4. AVERAGEIFS's average_range and criteria_range arguments where changed to infinite ranges, B$2:B & A$2:A instead of B$2:B$12 & A$2:A$12. This way the formula will automatically include new dates and durations as they are added.

Cell	Duration	Actual Number Stored	Stored * 14,400
E2	0:06:15	0.00434027777777778	6.25
E3	0:12:27	0.00864583333333333	12.45

Multi-Row Formula

1. Wraps the Basic Formula in the ARRAYFORMULA function
2. Uses LEN in an IF function to check if each row in column D contains a lookup value.
   1. value_if_true argument is the Basic Formula but the AVERAGEIFS's criteria argument is now the range D2:D instead of the cell D2.
   2. value_if_false argument is an empty value.