1

I wanna add times for example 12:24 or 25:32 and then filter out the fastest or the average time.

I'm doing a sheet for dungeon runtimes. I wanna enter the times in a format like 25:52 (mm:ss) or similar. Then I wanna filter out the fastest run as well as the average time to run a dungeon.

Here's a list of times that get converted easily with this line. The problem with this is that I can't filter on the results.

19:55
20:10
22:35
25:44
27:22

=ARRAYFORMULA(IF(A:A<>"", TEXT("00:"&A:A, "mm:ss"), ))

I've encountered problems formating the time. Either i need to add the times as 0:25:32 or it gets converted into 1:32 AM. If I convert the times as shown in this post. I'm getting troubles then filtering out the fastest time. Google Sheets format time incorrectly

player0
  • 124,011
  • 12
  • 67
  • 124
Pelleplast
  • 13
  • 3

1 Answers1

0

SUM:

=ARRAYFORMULA(TEXT(SUM(IF(A:A<>"", TIME(0, LEFT(A:A, 2), RIGHT(A:A, 2)), )), "[mm]:ss"))

AVG:

=ARRAYFORMULA(TEXT(AVERAGE(IF(A:A<>"", TIME(0, LEFT(A:A, 2), RIGHT(A:A, 2)), )), "[mm]:ss"))

MIN:

=ARRAYFORMULA(TEXT(MIN(IF(A:A<>"", TIME(0, LEFT(A:A, 2), RIGHT(A:A, 2)), )), "[mm]:ss"))

0

or if you want hours too then use "[h]:mm:ss"

also format your A column as Plain text if you enter time in format MM:SS

player0
  • 124,011
  • 12
  • 67
  • 124