# Return averageifs of durations formatted to display only minutes

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? # 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.