SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

Calculate Average time in Excel

(10 posts)
  • Started 2 years ago by HunterHunted
  • Latest reply from moreeg
  • Topic Viewed 9940 times

HunterHunted
Posts: 25

Dears

I have some time data. And I want to calculate the Average Time for each column . Kindly calculate the average time for each column Highlithted by red color. And please share how did you find the average time.

Download Link for file

http:​/​/​ubuntuone.com/​7mPhxGciOUL6MK0dlA3Oia

Posted 2 years ago
Top
 
Zagreus
Posts: 446

Hunterhunted. I'm no expert in this field but if you try the following and see if it helps. =AVERAGE(A2:A10) for instance.

Daveh.

Posted 2 years ago
Top
 
Enthusiast
Posts: 566

If those numbers on your spreadsheet are supposed to be elapsed times for generating backups then you are entering them incorrectly. You have them formatted as time, they should be number. Then you can average them with the function described above. However, the zero values in your data (no backup performed that day?) are factored into your average, bringing the value down. You may want to exclude zero values in the data.

Posted 2 years ago
Top
 
moreeg
Posts: 842

Hey Vincent

I couldn't see the OP's link but Zagreus' answer seemed right to me since I have a spreadsheet that does average times and mine are formatted as time. In fact, I'm tracking times in minutes and seconds but recording them as hours and minutes and it still works - unless you show me the flaw in my logic. Here is a shot of my spreadsheet - at the top you'll see that it is formatted in time as hours/minutes ....

Posted 2 years ago
Top
 
Enthusiast
Posts: 566

Well, I'm at work here so I don't get to see attached graphics. I will look at it once I am home.

Posted 2 years ago
Top
 
moreeg
Posts: 842

Sorry ... I was trying out Comcast because it has a direct upload from Jing but apparently no way to embed the image. I've switched back to Photobucket now.

here are the data in case you want to play

6:59
7:15
7:03
7:35
6:29
7:03
8:27
7:30
7:20
7:35
7:19
8:47
6:46
7:55
8:06
6:39
7:47
9:08
6:03
8:06
6:42
7:01
6:33
7:06
7:17
7:48
9:23
7:05
7:03
7:16
Average
7:26

Posted 2 years ago
Top
 
Enthusiast
Posts: 566

Yes, the time formatting will work, however, the OP has several instances of zero in the raw data, so to get the average elapsed time only for the days that the backup was actually run, the formula should be =AVERAGEIF(A2:A10,"<>0"), where A2:A10 is replaced by the data range.

Posted 2 years ago
Top
 
moreeg
Posts: 842

That same formula works on data formatted as time.

Posted 2 years ago
Top
 
Enthusiast
Posts: 566

yeah, the averageif() will ignore the zero data fields, is all.

Posted 2 years ago
Top
 
moreeg
Posts: 842

Fair enough - as this is an academic discussion seeing as how the OP has disappeared you might want to look at this post. Historically, Amrinder seems to respond better to you so you may want to weigh in with your version of a solution - it may evoke a response.

Posted 2 years ago
Top
 



Topic Closed

This topic has been closed to new replies.