7

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.

8

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

sample screed

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.

|improve this answer|||||
  • 1
    I think I know what happened. I had the fields as Time but I had average as decimal. When I changed it to time it worked. I then was able to use format cells and bring it down to mm:ss. – chobo2 Jan 30 '10 at 21:07
  • A very quick and efficient method, +1. – John T Jan 30 '10 at 23:12
  • Unfortunately this doesn't ignore zero duration fields which means that if you have e.g. a timesheet for a month for work, your average hour will be wrong until the end of the month. I did not get AVERAGEIF to work properly. Any ideas? – Julian F. Weinert Jan 15 '15 at 11:41
  • @Julian - I disagree, it is actually doing it's job - an average of 3,6,0 across three fields makes for an average 3... If you don't want certain cells to appear in the average, don't include them, but I don't believe this is a problem. – William Hilsum Jan 15 '15 at 16:55
  • I mean TIME DURATION, so the cell type must be duration and the values would be 00:35,00:00,08:46. doesn't work for me, might be a bug... – Julian F. Weinert Jan 15 '15 at 23:34
1

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

|improve this answer|||||
  • Hmm, I got 3:16 as you can see below. Weird... – Connor W Jan 30 '10 at 20:46
  • @Connor W - I retyped my values so they were really in "mm:ss" format rather than "hh:mm" format as Excel assumed they were when I pasted them into the sheet. It could be just rounding error in the function when dealing with "hh:mm" rather than "mm:ss" – ChrisF Jan 30 '10 at 20:49
  • I get 3:16:53, this is rounded up. – William Hilsum Jan 30 '10 at 20:49
  • @Wil - yes - as I was working in "mm:ss". With "hh:mm" the time will be truncated. – ChrisF Jan 30 '10 at 20:51
0

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."

|improve this answer|||||
0

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.

|improve this answer|||||
0

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.
|improve this answer|||||
0

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

|improve this answer|||||
0

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.

|improve this answer|||||
0

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.

|improve this answer|||||
  • 1
    The result of math functions doesn't depend on the format of the source cells. – RalfFriedl Jun 10 '19 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.