In Excel97, I have the following times i would like to sum.
(Minutes:Seconds:Seconds/100).


A
-------------
1 07:56:07
-------------
2 05:22:76
-------------
3 05:14:84
-------------
4 05:41:51
-------------
5 05:14:01
-------------
6 05:28:25
-------------
7 07:56:37
-------------
-------------
8 41:33:81



If i sum, i get 41:33:81, but the correct result for
this case is 42:53:81 (Minutes:Seconds:Seconds/100).

I would like the result to be in one single cell,
that in this case is cell A8.

What formula should i write in cell A8 ?

Any Help appreciated.

Robert.

Bubu,

I did =SUM(A1:A7) and got 42:53.81 (sic!).

You might want to format it as [mm]:ss.00 in case it goes over an hour.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bubu" wrote in message
om...
In Excel97, I have the following times i would like to sum.
(Minutes:Seconds:Seconds/100).


A
-------------
1 07:56:07
-------------
2 05:22:76
-------------
3 05:14:84
-------------
4 05:41:51
-------------
5 05:14:01
-------------
6 05:28:25
-------------
7 07:56:37
-------------
-------------
8 41:33:81



If i sum, i get 41:33:81, but the correct result for
this case is 42:53:81 (Minutes:Seconds:Seconds/100).

I would like the result to be in one single cell,
that in this case is cell A8.

What formula should i write in cell A8 ?

Any Help appreciated.

Robert.

Perhaps I'm just confused. I'm not sure how you're even displaying

05:22:76

much less using it to calculate hundredths of seconds. For me, that
entry converts to 05:23:16.

If your times are really in minutes:seconds:hundredths, then you're
going to have to do some manipulation he

First, if that's the case, your "correct result" should be 42:54:61, not
42:53:81.


Second, XL doesn't understand hundredths of seconds using the colon - it
interprets it as hh:mm:ss, where ss is base-60, not base-100. So you can
never have a result where the last two digits are 60 or above.

You could format your data as

Format/Cells/Number/Custom [mm]:ss.00

and reenter your data as:

A1: 7:56.07
A2: 5:22.76
....
A8: =SUM(A1:A7) == 42:54.61

and XL will display the (true) correct result.


If you want to keep using hh:mm:ss to stand for mm:ss:hundredths, you
can use this array-entered formula (CTRL-SHIFT-ENTER or CMD-RETURN):


=SUM(INT(A1:A7*1440),SECOND(A1:A7)/100)/86400

which returns 42:54.61 when formatted as above.





In article ,
(Bubu) wrote:

In Excel97, I have the following times i would like to sum.
(Minutes:Seconds:Seconds/100).


A
-------------
1 07:56:07
-------------
2 05:22:76
-------------
3 05:14:84
-------------
4 05:41:51
-------------
5 05:14:01
-------------
6 05:28:25
-------------
7 07:56:37
-------------
-------------
8 41:33:81



If i sum, i get 41:33:81, but the correct result for
this case is 42:53:81 (Minutes:Seconds:Seconds/100).

I would like the result to be in one single cell,
that in this case is cell A8.

What formula should i write in cell A8 ?

Any Help appreciated.

Robert.

"JE McGimpsey" wrote in message
...
Perhaps I'm just confused. I'm not sure how you're even displaying

05:22:76

much less using it to calculate hundredths of seconds. For me, that
entry converts to 05:23:16.


It is 05:22.76 (format is mm:ss.00)

On 28 Feb 2004 10:04:18 -0800, (Bubu) wrote:

In Excel97, I have the following times i would like to sum.
(Minutes:Seconds:Seconds/100).


A
-------------
1 07:56:07
-------------
2 05:22:76
-------------
3 05:14:84
-------------
4 05:41:51
-------------
5 05:14:01
-------------
6 05:28:25
-------------
7 07:56:37
-------------
-------------
8 41:33:81



If i sum, i get 41:33:81, but the correct result for
this case is 42:53:81 (Minutes:Seconds:Seconds/100).

I would like the result to be in one single cell,
that in this case is cell A8.

What formula should i write in cell A8 ?

Any Help appreciated.

Robert.

You could use the array formula:

=SUM(--("0:"&SUBSTITUTE(A3:A15,":",".",2)))

and format as [m]:ss.00

or if you really want colons as separators throughout


=SUBSTITUTE(TEXT(SUM(--("0:"&SUBSTITUTE(A3:A15,":",".",2))),"[m]:ss.00"),".",":")

In the latter case, the result will be text and not a number that Excel can
manipulate.

To *array-enter* a formula, hold down <ctrl<shift while hitting <enter. XL
will place brackets {...} around the formula.


--ron

Ah, well, in that case I was confused.

But in that case, why was the OP's SUM() off? Still confused.

In article ,
"Bob Phillips" wrote:

It is 05:22.76 (format is mm:ss.00)

Don't know, it worked fine in my test, and Bubu hasn't posted back.

Bob

"JE McGimpsey" wrote in message
...
Ah, well, in that case I was confused.

But in that case, why was the OP's SUM() off? Still confused.

In article ,
"Bob Phillips" wrote:

It is 05:22.76 (format is mm:ss.00)