Averaging Times

[Guest]

I need to average a list of minutes and seconds in mm:[ss] format (the only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get Excel to do
this correctly?

 

[Guest]

use AVERAGE

=AVERAGE(A1:A5)

gave me a result of 00:36

Cells were formatted as mm:ss and data as

00:00:35
00:00:40
etc

 

[Guest]

That did not work for me. It produced a #DIV/0! error. When I use AVERAGEA
it produces a number rather than an error, but it's the wrong number as
described below.

Other ideas?

use AVERAGE

=AVERAGE(A1:A5)

gave me a result of 00:36

Cells were formatted as mm:ss and data as

00:00:35
00:00:40
etc

I need to average a list of minutes and seconds in mm:[ss] format (the only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get Excel to do
this correctly?

 

[Guest]

Were cells formatted as mm:ss? I cannot see why it doesn't work ( I have
Excel 2003)

That did not work for me. It produced a #DIV/0! error. When I use AVERAGEA
it produces a number rather than an error, but it's the wrong number as
described below.

Other ideas?

use AVERAGE

=AVERAGE(A1:A5)

gave me a result of 00:36

Cells were formatted as mm:ss and data as

00:00:35
00:00:40
etc

I need to average a list of minutes and seconds in mm:[ss] format (the only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get Excel to do
this correctly?

 

[Guest]

Yes, I changed the format from mm:[ss] to mm:ss.

Were cells formatted as mm:ss? I cannot see why it doesn't work ( I have
Excel 2003)

That did not work for me. It produced a #DIV/0! error. When I use AVERAGEA
it produces a number rather than an error, but it's the wrong number as
described below.

Other ideas?

use AVERAGE

=AVERAGE(A1:A5)

gave me a result of 00:36

Cells were formatted as mm:ss and data as

00:00:35
00:00:40
etc

:

I need to average a list of minutes and seconds in mm:[ss] format (the only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get Excel to do
this correctly?

 

[David Biddulph]

If your cell is formatted as mm:[ss] , the mm is months, not minutes. For
the m to be minutes use mm:ss or m:ss

You may therefore have different numbers from what you think you've got
(either for your data values or for your result, or both). Look at what's
in the formula bar, or try formatting temporarily to General or Number and
see whether the 0:35 comes out as 0.000405092592592593

 

[Dallman Ross]

I need to average a list of minutes and seconds in mm:[ss] format (the only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get
Excel to do this correctly?

Excel 2002 SP2 does it fine. I pasted the above into a sheet and
ran =AVERAGE(A1:A5) and got 0:36. What are you doing differently?
My format says Custom, h:mm when I go look.

=dman=

 

[David Biddulph]

The answer you're getting with AVERAGEA is displaying as 1:00 because you
get an average of zero (which you'll see if you reformat the cell
temporarily to General or Number). See my previous post re the strange
formatting of mm:[ss] giving the month value.

If you're getting zero from that and a #DIV/0! from AVERAGE, then I guess
that your input cells are text, not time values. Again see my previous post
to check what you've got.
--
David Biddulph

That did not work for me. It produced a #DIV/0! error. When I use
AVERAGEA
it produces a number rather than an error, but it's the wrong number as
described below.

Other ideas?

use AVERAGE

=AVERAGE(A1:A5)

gave me a result of 00:36

Cells were formatted as mm:ss and data as

00:00:35
00:00:40
etc

I need to average a list of minutes and seconds in mm:[ss] format (the
only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get Excel to
do
this correctly?

 

[Guest]

When I change the format to General, the number displays the same, "00:35,"
in the formula bar.

If your cell is formatted as mm:[ss] , the mm is months, not minutes. For
the m to be minutes use mm:ss or m:ss

You may therefore have different numbers from what you think you've got
(either for your data values or for your result, or both). Look at what's
in the formula bar, or try formatting temporarily to General or Number and
see whether the 0:35 comes out as 0.000405092592592593
--
David Biddulph

I need to average a list of minutes and seconds in mm:[ss] format (the only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get Excel to do
this correctly?

 

[Guest]

I tried formatting as h:mm and got the #DIV/0! error again. However, I also
found that regardless of the format I select, the numbers appear the same
(e.g., 00:55). They were brought in from another program and converted from
text to numbers using Excel's automatic conversion pop-up.

Does that provide any helpful clues about the problem?

I need to average a list of minutes and seconds in mm:[ss] format (the only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get
Excel to do this correctly?

Excel 2002 SP2 does it fine. I pasted the above into a sheet and
ran =AVERAGE(A1:A5) and got 0:36. What are you doing differently?
My format says Custom, h:mm when I go look.

=dman=

 

[David Biddulph]

And it presumably dioesn't change what's displayed in the cell? In which
case it confirms that you've got text, not a time [which would be confirmed
if you try ISTEXT(A1) or ISNUMBER(A1)].
You may have spurious spaces floating about. Try selecting a blank cell,
copying it, then selecting your "times", and using edit/ paste special/ add.
There may be other spurious non-printing characters. CLEAN may help, after
which you may be able to convert as outlined a couple of sentences ago.
--
David Biddulph

When I change the format to General, the number displays the same,
"00:35,"
in the formula bar.

If your cell is formatted as mm:[ss] , the mm is months, not minutes.
For
the m to be minutes use mm:ss or m:ss

You may therefore have different numbers from what you think you've got
(either for your data values or for your result, or both). Look at
what's
in the formula bar, or try formatting temporarily to General or Number
and
see whether the 0:35 comes out as 0.000405092592592593
--
David Biddulph

I need to average a list of minutes and seconds in mm:[ss] format (the
only
format that would display them properly):
0:35
0:40
0:39
0:42
0:26

Excel 2003 SP2 is giving 1:00 rather than 00:36. How do I get Excel to
do
this correctly?