Google sheets average a series of durations of the form MM:SS[.\d ]-CodePudding

Home > database > Google sheets average a series of durations of the form MM:SS[.\d ]

Google sheets average a series of durations of the form MM:SS[.\d ]

Time：11-01

I have tried the answers

CodePudding user response：

A little bit simpler option:

=ArrayFormula(AVERAGE(TIMEVALUE("0:"&B2:B8)))

format result cell as:

CodePudding user response：

try:

=INDEX(TEXT(AVERAGE(VALUE(SUBSTITUTE(
 FILTER(B2:B, B2:B<>""), ".", ":"))), "[h]:m.s"))

CodePudding user response：

more accurate solution:

=INDEX(TEXT(AVERAGE(QUERY(QUERY(SPLIT(
 FILTER(B2:B, B2:B<>""), ":")/24/60/{1,60}, 
 "select Col1 Col2"), "offset 1", 0)), "m:s.0"))