Formula to calculate average time

[sharky12345]

Guys,

I have a spreadsheet which has various columns where users enter details of issues that occur. 1 of these columns has the time the issue was noticed and another the time the issue was concluded.

From those 2 columns I have been able to calculate the average time that an issue takes to resolve, (columns are formatted as h:mm), but is there a way I can get Excel to calculate the average time that an issue occurs? This is the time on the clock that it happens.

To be more precise, I want Excel to work out if there is a pattern to when these issues arise and therefore tell me what the 'average' clock time is that they crop up.

 

[barry houdini]

Have you tried just averaging the column with the time the issue was noticed?

 

[sharky12345]

Barry,

I hand't thought of that - but if I do will Excel just average it as an amount of time rather than the actual time on the clock if that makes sense?

 

[sharky12345]

Barry,

Disregard my last - tried it and it worked.

Sometimes things are so obvious you just can't see them!

 

[sharky12345]

Actually - it doesn't work because the formula is just calculating it as a quantity of time.

I've just tried 23:59 to 03:00 and it has given me 13:29 which is not what I want - can anyone help?

 

[barry houdini]

If you have times either side of midnight then it gets a bit trickier, presumably you want the average of 23:59 and 03:00 to be approx 01:30....but what if you had three times like 05:00, 12:00 and 20:00, what's the average of those?

Are you averaging times which could occur any time of day or within a specific period?

 

[sharky12345]

Barry - yes, the issues can occur at any point during a 24hr period and sometimes do go across midnight.

Should I put this problem to one side or do you think there is a solution?

 

[Jerry Sullivan]

sharky12345,

Barry is pointing out that if you want to calculate the average of a group of times, you need to know the true relationship between all the values from a common baseline.

If your data set were the 5 times recorded below and you wanted to know the average, you could calculate that using the date & time values and arrive at 4:12 AM on 11/15/2011. This is different than the average value of 13:48 that you will get if you average the times only.

Excel Workbook
	A	B	C	D	E	F
1		Date & Time	Decimal Value		Time Only	Decimal Value
2		11/14/2011 20:00	40861.8333		20:00:00	0.8333
3		11/14/2011 21:00	40861.8750		21:00:00	0.8750
4		11/15/2011 05:00	40862.2083		5:00:00	0.2083
5		11/15/2011 11:00	40862.4583		11:00:00	0.4583
6		11/15/2011 12:00	40862.5000		12:00:00	0.5000
7
8	Averages-->	11/15/2011 04:12	40862.1750		13:48:00	0.5750
9
Sheet

However, taking a step back to the purpose of why you are analyzing this...

I want Excel to work out if there is a pattern to when these issues arise and therefore tell me what the 'average' clock time is that they crop up.

The average time of 4:12 AM doesn't help you understand the pattern and diagnose the problem.

Rather than calculating an average time, a better approach might be to chart the data then look for clusters and patterns.

 

[sharky12345]

Jerry,

Got that - many thanks for your help, I think I will go down the chart route.