SEARCH

The How-To Geek Forums Have Migrated to Discourse



Join the Discussion at discuss.howtogeek.com

How-To Geek Forums / Microsoft Office

Calculate Average time in Excel

(10 posts)
  • Started 8 months ago by HunterHunted
  • Latest reply from moreeg
  • Topic Viewed 2839 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 8 months ago #
Top
 
Zagreus
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 8 months ago #
Top
 
Enthusiast
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 8 months ago #
Top
 
moreeg
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 8 months ago #
Top
 
Enthusiast
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 8 months ago #
Top
 
moreeg
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 8 months ago #
Top
 
Enthusiast
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 8 months ago #
Top
 
moreeg
moreeg
Posts: 842

That same formula works on data formatted as time.

Posted 8 months ago #
Top
 
Enthusiast
Enthusiast
Posts: 566

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

Posted 8 months ago #
Top
 
moreeg
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 8 months ago #
Top
 



Topic Closed

This topic has been closed to new replies. Please create a new topic instead.


Create New Topic Return to Forum