Thread
:
Correct Time Sum, by Formulas. Help Please.
View Single Post
#
5
Posted to microsoft.public.excel.programming
Ron Rosenfeld
external usenet poster
Posts: 5,651
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
Reply With Quote
Ron Rosenfeld
View Public Profile
Find all posts by Ron Rosenfeld