Menu

Correct Time Sum, by Formulas. Help Please.

Search this Thread

Prev

Previous Post

Next Post

Next

#7

Report Post

Posted to microsoft.public.excel.programming

Ron Rosenfeld Ron Rosenfeld is offline

Ron Rosenfeld is offline

external usenet poster

Posts: 5,651

Default

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

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formulas showing as correct but not working	Lesley11356	Excel Worksheet Functions	3	August 14th 08 09:07 AM
Subtract hours from a time and get the correct time	casey	Excel Worksheet Functions	1	June 22nd 08 08:41 PM
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP	Sherberg	Excel Worksheet Functions	4	September 11th 07 01:34 AM
Can you please hel me to find the correct formulas	george24	Excel Worksheet Functions	1	January 11th 07 11:38 AM
F9 flips formulas between zero and correct value	Arun	Excel Discussion (Misc queries)	1	April 21st 06 02:29 PM

All times are GMT +1. The time now is 09:59 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement