Google-sheets – How to calculate the average of values in Col. B, if Col. A contains a unique timevalue of Col. C

formulasgoogle sheetsgoogle-sheets-arrayformulaworksheet-function

This is how my table looks:

(in column A)            (in Column B)    (in Column C should be)   (in Column D should be)
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.

Best Answer

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

Related Topic