Correct Time Sum, by Formulas. Help Please.

  • Thread starter Thread starter Bubu
  • Start date Start date
B

Bubu

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)
 
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 here:

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.
 
JE McGimpsey said:
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.
Click to expand...

It is 05:22.76 (format is mm:ss.00)
 
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.
Click to expand...

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.
 
Back
Top