# How to calculate the average of values in Col. B, if Col. A contains a unique timevalue of Col. C?

This is how my table looks:

A B C D
2016-01-01 08:00:00 80 08:00:00 240
2016-01-01 08:10:00 820 08:10:00 611.15
2016-01-01 08:20:00 85 08:20:00 314.15
2016-01-01 08:30:00 500 08:30:00 533
2016-01-02 08:00:00 400
2016-01-02 08:10:00 403
2016-01-02 08:20:00 544
2016-01-02 08:30:00 606
• I want to display in Column C the unique time values of Column A. This would mean: `08:00:00`, `08:10:00`, `08:20:00` and `08:30:00`.
• and in Column D the average value of Column B if Column A contains the unique value of Column C.

Once you get the `TIMEVALUE` of first column, You can use `QUERY` to group the data and find averages:

``````=QUERY(
{ARRAYFORMULA(TIMEVALUE(A1:A8)),B1:B8},
"Select
Col1,avg(Col2)
group by Col1
format Col1 'hh:mm:ss' ",
0
)
``````
• I like this approach Mar 7 at 9:40

Populating column C with the unique values in column A is easy.

in cell C1 put the formula

``````=unique(A:A)
``````

it will be treated as an array formula and populate the column.

The formula for the average is more complex, and has a manual portion.

``````=AVERAGEIF(A:A,C:C,B:B)
``````

The manual portion is that it has to be copied to each cell in column D that has a value in Column C.

C1: `=ARRAYFORMULA(IF(LEN(A1:A8);RIGHT(A1:A8;8)))`

D1: `=UNIQUE(C1:C8)`

E1: `=IFS( D1 = ""; ""; D1 <> ""; AVERAGEIF(\$C\$1:\$C\$8;\$D\$1:\$D\$8;\$B\$1:\$B\$8))`

# Single Formula in `C1`

``````=LET(
times,ARRAYFORMULA(MOD(1/(1/A:A),1)),
BYROW(UNIQUE(times), LAMBDA(row,
IFERROR({row, AVERAGEIF(
times, row, B:B)}))))
``````

# Explanation

### date_time vs. time

Dates, times, and date_time are all stored as numbers where 1 is equivalent to a day (24 hours). Any portion of a day (time) will necessarily be less than 1. So returning the time portion is as simple as either of:

1. Subtracting the Integer portion
`date_time-INT(date_time)=time`
2. Using the MOD function
`MOD(date_time, 1)=time`

### Date_Times Range to Times

``````=ARRAYFORMULA(MOD(1/(1/A:A),1))
``````

### Unique Times

``````=UNIQUE(times)
``````

### Combined Formula

``````=LET(
times,ARRAYFORMULA(MOD(1/(1/A:A),1)),
BYROW(UNIQUE(times), LAMBDA(row,
IFERROR({row, AVERAGEIF(
times, row, B:B)}))))
``````

-BYROW passes each unique time row-by-row into a LAMBDA function stored as `row`

• LAMBDA's formula returns a two-column array for each `row` passed in:
• value of `row` in column 1
• Value of AVERAGEIF function in Column 2
• IFERROR returns nothing instead of errors