software

I need help, I received link references from a corporate spreadsheet with
time format 13:30 (representing mm:ss). I am trying to get at SUM and AVERAGE
of these cells and having a hard time getting it right. I did try to have
output format as [m]:ss and it is coming out incorrectly. If I have to
convert these to h:mm:ss is there a way to make to conversion happen
automatically.

Example:

20:12
5:31
9:47
8:45
8:27
7:25
20:21
17:46
13:01
22:17

SUM these to format [m]:ss will give me 15212:00
SUM these to format mm:mm will give me 32:00
AVERAGE these to format 13:30 will give me 1:21

Can someone explain what is Excel doing here and possible offer me a
solution for AVERAGE these times.

Thanks,

Kha

Although these may look like minutes and seconds, I think Excel has
taken them as hours, minutes and seconds and is just displaying [h]:mm.
Click on one of the cells and check the format with Format | Cells |
Number (tab).

If this is the case, then in a blank column enter the formula:

=A1/60, assuming your times are in column A, and format as [m]:ss. Copy
this down.

You can fix the values using lt;copygt; with Edit | Paste Special | Values
| OK, and you can also paste the fixed values to over-write the
original values. Hopefully, your SUM and AVERAGE formulae will work
after this.

Hope this helps.

PeteThe cell format is actually Timegt;13:30

quot;Petequot; wrote:

gt; Although these may look like minutes and seconds, I think Excel has
gt; taken them as hours, minutes and seconds and is just displaying [h]:mm.
gt; Click on one of the cells and check the format with Format | Cells |
gt; Number (tab).
gt;
gt; If this is the case, then in a blank column enter the formula:
gt;
gt; =A1/60, assuming your times are in column A, and format as [m]:ss. Copy
gt; this down.
gt;
gt; You can fix the values using lt;copygt; with Edit | Paste Special | Values
gt; | OK, and you can also paste the fixed values to over-write the
gt; original values. Hopefully, your SUM and AVERAGE formulae will work
gt; after this.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;

Yes, if you do Format | Cells | Number (tab) then it shows Time with
13:30 - this means hours and minutes. Click on 13:30:55 (two down on
the list) and you should see your first value change to 20:12:00, in
which case it is formatted as I suggested and you can follow my
suggestions to rectify it.

PeteNow, I am really in trouble because I want them the be in mm:ss not hh:mm:ss
i.e. the first number should be 0:20:12. Can you help with this? or another
forum is needed here.

quot;Petequot; wrote:

gt; Yes, if you do Format | Cells | Number (tab) then it shows Time with
gt; 13:30 - this means hours and minutes. Click on 13:30:55 (two down on
gt; the list) and you should see your first value change to 20:12:00, in
gt; which case it is formatted as I suggested and you can follow my
gt; suggestions to rectify it.
gt;
gt; Pete
gt;
gt;

Read the second part of my first posting, ie:

quot; ...
If this is the case, then in a blank column enter the formula:

=A1/60, assuming your times are in column A, and format as [m]:ss. Copy

this down.

You can fix the values using lt;copygt; with Edit | Paste Special | Values
| OK, and you can also paste the fixed values to over-write the
original values. Hopefully, your SUM and AVERAGE formulae will work
after this.

Hope this helps.
....quot;

And I hope this re-posting helps.

Pete