How to get Average for time in Excel 2007?

Question

I have these values

01:15
05:00
01:31
02:00
02:21
02:39
03:29
08:00

I highlighted all these cells and went to format cells -> custom -> and choose mm:ss

I then tried to use the built in average function in Excel 2007

=AVERAGE(D31:D38)

The result is 0.0

Of course this is not the number result that it should be(I have not calculated it manually yet but I am sure it is not 0).

I am not sure if it has the fact to do when you click on the cell it has something like this

"12:08:00 AM"

I am not sure if that is what is screwing it up.

Answer 1

If you just want to do this quickly, select Time as the option from the drop down box and it should work as expected:

I have tried and cannot replicate your results, I think that you are messing up hours/minutes/seconds, Time fields are usually stored as hh:mm:ss, and then just displayed how you want. I recommend you try just using the built in Time field (as above) then try changing it later to hh:mm / mm:ss / hh:mm:ss, I think what is happening is you are storing as mm:ss, and displaying the average as hh:mm, or similar which is why you are getting weird results.

Answer 2

To get the times formatted as "mm:ss" I had to enter them as follows:

00:01:15
00:05:00
00:01:31
00:02:00
00:02:21
00:02:39
00:03:29
00:08:00

i.e. zero hours, some minutes and some seconds.

Changing the format to Time displays the "00:" for the hours.

Then when I average them I get 03:17

Answer 3

One thing to watch for when setting time formats in excel is that the format "hh:mm" ignores the number of days and similarly "mm:ss" will ignore the number hours entered in the cell.

If you are getting strange looking results when manually calculating average times try putting square brackets around highest level unit of time in your format:

• [hh]:mm
• [mm]:ss

This will return the absolute number of hours or minutes - useful if your sum of times goes over 24 hours or 60 minutes

Answer 4

Simple Just click on Cell and Convert into 12 hr time

i.e. if you have 00:31:24 then convert it into Full date like this 12:31:24 AM.

After that you can use Average.

Answer 5

If you want duration time, not time of day, use the format "[h]mm:ss" and not the format "mm:ss". Normal math functions (e.g. average, sum, etc.) should then work.

Answer 6

I was trying to do some math on time values, and I ended up here at this page looking for help. Strangely, though, none of the cell format suggestions listed here were working for me...

...until I finally realized that my data, which I'd imported from a file, had spaces in front of most of the time values. Once I removed the spaces, Excel was HAPPY to run all manner of formulas, correctly, on my time data.

Something to look for if you're having trouble for what seems like "no reason."

Answer 7

First, highlight all of the cells that you will be using for the calculation. (The cells containing the values that you will be adding, and the cell containing the average of all of the times.) Right click the collective highlighted cells, and click "Format Cells". In the Number Tab, choose Custom from the selections on the left side of the window. In the type drop down selection box, look for the value "h:mm". Select that option, and click ok.

The formula for the cell containing the average will be the same as any other type of average ( =SUM(XX:XX)/X ,OR =AVERAGE(XX:XX) )

Using this format, I'm given values such as this

3:55 3:58 4:14 3:22 3:49 4:07 4:02

AVG = 3:55

I hope this helps you out.

Answer 8

The approached I used with MS Excel 2010 (which will be similar to MS Excel 2007) was:

1. Convert your time data into a decimal number. To do that just use the Format Cells dialog box; in the Number tab, click on the Number category. This will change all time data into a number.
2. Calculate the average from that data and lastly re-convert that number into a time figure using the same approach described in step 1.