How to Average elapsed time values?

[MTB_AR]

I have a column with elapsed time values like 01:45, 02:25, 01:58, etc. I
can't seem to get the average to work out right using other posts, so can
anyone help me out! I have 667 values in colum M. All the values have a 0
as first number.

If I remove the 0 from the time, I have no problem, but that would take
forever, at least with my skill level!

 

[Bob Phillips]

Sounds like they are text. Try this

=AVERAGE(IF(D1100<>"",--(D1100)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Stan Schwartz]

What is the double dashes do in the formula? I can't find a reference for that anywhere....

Thank you in advance,
Stan



Bob Phillips wrote:

Re: How to Average elapsed time values?
24-Jul-08

Sounds like they are text. Try thi

=AVERAGE(IF(D1100<>"",--(D1100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually
When editing the formula, it must again be array-entered


--
HT

Bo

(there's no email, no snail mail, but somewhere should be gmail in my addy


EggHeadCafe - Software Developer Portal of Choice
Resolving the ASP.NET Database Security Dilemma
http://www.eggheadcafe.com/tutorial...b7-dda71223faa0/resolving-the-aspnet-dat.aspx

 

[Stan Schwartz]

Found it. Unary, slick. Very slick.



Stan Schwartz wrote:

How to Average elapsed time values?
05-Oct-09

What is the double dashes do in the formula? I can't find a reference for that anywhere....

Thank you in advance,
Stan

EggHeadCafe - Software Developer Portal of Choice
Screen.MousePointer = hand in VB 6.0
http://www.eggheadcafe.com/tutorial...9b6-8610d3712835/screenmousepointer--han.aspx