0

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.
0

4 Answers 4

1

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
)
1
  • 1
    I like this approach
    – Blindspots
    Mar 7 at 9:40
0

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.

0
0

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))

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.