| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | How to get the average of times while skipping over empty cells? | |||||||||||
2 | https://webapps.stackexchange.com/questions/136085/how-to-get-the-average-of-times-while-skipping-over-empty-cells | as number | as time | |||||||||
3 | FILTER | |||||||||||
4 | 08:00:00 | 08:10:00 | 08:10:00 | 08:10:00 | 08:20:00 | 0.3333333333 | 08:00:00 | =AVERAGE(TIMEVALUE(FILTER(B4:F4,B4:F4<>""))) | ||||
5 | 0.3402777778 | 08:10:00 | =ArrayFormula(AVERAGE(TIMEVALUE(FILTER(B4:H4,B4:H4<>"")))) | |||||||||
6 | ||||||||||||
7 | 08:00:00 | 08:10:00 | 08:10:00 | 08:10:00 | 08:20:00 | 0.3333333333 | 08:00:00 | =AVERAGE(TIMEVALUE(FILTER(B7:F7,B7:F7<>""))) | ||||
8 | 0.3402777778 | 08:10:00 | =ArrayFormula(AVERAGE(TIMEVALUE(FILTER(B7:H7,B7:H7<>"")))) | |||||||||
9 | ||||||||||||
10 | ||||||||||||
11 | IFERROR | |||||||||||
12 | 08:00:00 | 08:10:00 | 08:10:00 | 08:10:00 | 08:20:00 | 0.3402777778 | 08:10:00 | =ARRAYFORMULA(AVERAGE(IFERROR(TIMEVALUE(B12:H12),))) | ||||
13 | 0 | 00:00:00 | =AVERAGE(IFERROR(TIMEVALUE(B12:H12),)) | |||||||||
14 | ||||||||||||
15 | 08:00:00 | 08:10:00 | 08:10:00 | 08:10:00 | 08:20:00 | 0.3402777778 | 08:10:00 | =ARRAYFORMULA(AVERAGE(IFERROR(TIMEVALUE(B15:H15),))) | ||||
16 | 0 | 00:00:00 | =AVERAGE(IFERROR(TIMEVALUE(B15:H15),)) | |||||||||
17 | ||||||||||||
18 | ||||||||||||
19 |