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?

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

"Mashuganah" wrote:

> 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?

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?

"Toppers" wrote:

> 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
>
> "Mashuganah" wrote:
>
> > 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?

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

"Mashuganah" wrote:

> 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?
>
> "Toppers" wrote:
>
> > 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
> >
> > "Mashuganah" wrote:
> >
> > > 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?

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


"Toppers" wrote:

> Were cells formatted as mm:ss? I cannot see why it doesn't work ( I have
> Excel 2003)
>
> "Mashuganah" wrote:
>
> > 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?
> >
> > "Toppers" wrote:
> >
> > > 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
> > >
> > > "Mashuganah" wrote:
> > >
> > > > 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?

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

"Mashuganah" <(E-Mail Removed)> wrote in message
news:A54EFD39-DCE5-4030-84C7-(E-Mail Removed)...
>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?

In <A54EFD39-DCE5-4030-84C7-(E-Mail Removed)>, Mashuganah
<(E-Mail Removed)> spake thusly:

> 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=

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

"Mashuganah" <(E-Mail Removed)> wrote in message
news:4313CA9F-8C06-4A14-9E5D-(E-Mail Removed)...
> 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?
>
> "Toppers" wrote:
>
>> 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
>>
>> "Mashuganah" wrote:
>>
>> > 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?

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

"David Biddulph" wrote:

> 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
>
> "Mashuganah" <(E-Mail Removed)> wrote in message
> news:A54EFD39-DCE5-4030-84C7-(E-Mail Removed)...
> >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?
>
>
>

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?


"Dallman Ross" wrote:

> In <A54EFD39-DCE5-4030-84C7-(E-Mail Removed)>, Mashuganah
> <(E-Mail Removed)> spake thusly:
>
> > 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=
>