Correct Time Sum, by Formulas. Help Please.
 

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.

Correct Time Sum, by Formulas. Help Please.
 

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.

Correct Time Sum, by Formulas. Help Please.
 

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.

Correct Time Sum, by Formulas. Help Please.
 

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

Correct Time Sum, by Formulas. Help Please.
 

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

Correct Time Sum, by Formulas. Help Please.
 

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)

Correct Time Sum, by Formulas. Help Please.
 

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)