CODEWITHSUNDEEP
excelexcel-2007
Maro
Maro

Reputation: 2629

Average time in milliseconds

I cannot get the average of times in milliseconds, using the average function returns #DIV/0!

And why excel detect that i'm dividing by zero? what function can i use to return the average of the following:

00:00:00.2154647
00:00:00.2452687
00:00:00.2631118
00:00:00.2634366
00:00:00.0500521
00:00:00.0468655
00:00:00.0034620
00:00:00.0052060
00:00:00.0040070
00:00:00.0037784
00:00:00.0042271
00:00:00.0037236
00:00:00.0032318
00:00:00.0047810

the cell formatted to Time.

Upvotes: 2

Views: 4678

Answers (2)

Markus
Markus

Reputation: 693

see this answer to convert time to milliseconds

convert to milliseconds

you use 7 digits for milliseconds so you for the milliseconds

=RIGHT(A1;7) / 100000

then Average the Results

=AVERAGE(B1:B??)

Upvotes: 5

barry houdini
barry houdini

Reputation: 46451

Excel only recognises milliseconds, i.e. 3 decimal places after the seconds, as you have more decimals you must have text values, which is why you get #DIV/0! error, that's the standard result when there are no numbers in the range.

You can still average those values unchanged, though, with an array formula, e.g. with data in A2:A15 use this formula

=AVERAGE(A2:A15+0)

confirm with CTRL+SHIFT+ENTER

format result cell as hh:mm:ss.000 and you should get 00:00:00.080 as K_B says

There can't be any real text values in the range otherwise you get an error

using the +0 converts text values to times, although you'll lose the "precision" of the extra decimals

.....or you can convert your data to real times like this

Select column of data then use Data > Text to columns > Finish > format range as hh:mm:ss.000

That should convert to real time values and you can use a regular AVERAGE function to average those

Upvotes: 0

Related Questions