Calculate average times

[tarumatu]

Hi, i'm having a hard time working out average pageview times on a website. I've already had a look on a number of posts but nothing seems to work, i've tried right-clicking the data range and formatting to time or custom as hh:mm:ss as my times are displayed as hours mins secs. I've tried the standard =average(data range), as well as =TEXT(AVERAGE(a1:a1000),"[hh]:mm:ss").Each time i get an error #DIV/0!. I dont understand why it doesnt work, could it be because the data range is from over 1500 rows of data? Can anyone help?

 

[oldbrewer]

ok try adding just the first 10

if that works the first 20

if that works the first 40 etc

 

[SpillerBD]

I think i understand. If i add up all the data ranges with =sum(a1:a1234) i get 00:00:00

This suggest the cells still contain Text not time values. Changing the format of text will not change the underlying value in the cell.
Use Text to Columns to hopefully get those changed to actual number/time values.

 

[tarumatu]

I'm still having the same problems, its strange how it doesnt sum/average the data. If i type in some times, then it works. But my list of data just won't calculate averages

 

[oldbrewer]

so your list of data is suspect - copy and paste on here the first 20 data values

 

[tarumatu]

[TABLE="width: 95"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Time on site[/TD]
[/TR]
[TR]
[TD]00:00:19[/TD]
[/TR]
[TR]
[TD]00:00:29[/TD]
[/TR]
[TR]
[TD]00:00:06[/TD]
[/TR]
[TR]
[TD]00:00:07[/TD]
[/TR]
[TR]
[TD]00:00:45[/TD]
[/TR]
[TR]
[TD]00:00:54[/TD]
[/TR]
[TR]
[TD]00:01:41[/TD]
[/TR]
[TR]
[TD]00:00:54[/TD]
[/TR]
[TR]
[TD]00:00:49[/TD]
[/TR]
[TR]
[TD]00:01:05[/TD]
[/TR]
[TR]
[TD]00:00:41[/TD]
[/TR]
[TR]
[TD]00:00:45[/TD]
[/TR]
[TR]
[TD]00:00:11[/TD]
[/TR]
[TR]
[TD]00:00:30[/TD]
[/TR]
[TR]
[TD]00:01:31[/TD]
[/TR]
[TR]
[TD]00:00:41[/TD]
[/TR]
[TR]
[TD]00:00:24[/TD]
[/TR]
[TR]
[TD]00:00:49[/TD]
[/TR]
[TR]
[TD]00:00:18[/TD]
[/TR]
[TR]
[TD]00:00:23[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

these add perfectly for me - just add your first 100, then 200, when does the error become apparent ?

 

[tarumatu]

I'd already tried it myself, whether its 2, 20 or 100. I guess i should redownload the file to see if anything was corrupted.

 

[tarumatu]

It looks like some strange formatting is going on in the file as i redownloaded it and problem reappears. I did a quick test by re-copying the data i sent you off this site, added it to a new excel sheet and =average worked!

 

[oldbrewer]

add the first 100 and divide by 100

 

[tarumatu]

you cant do any calculations, not even =sum etc. I copied the data, pasted into notepad, copied back into a new excel sheet and then it worked! What a pain...