average of times
 

i have times listed as "h:mm" and i would like to show the average. note that
the times are calculated, not entered manually.
formula in time row: =TEXT(C2-B2, "h:mm")

what formula would i put in the cell where i want the average of all times?
thanks for your help.

average of times
 

M,

If these formulas were in D2:D4, you could use =AVERAGE(TIMEVALUE(D2:D4)). This is an array
formula, and must be commited to the cell with Ctrl-Shift-Enter, not just Enter.

It might be simpler to just keep your times in true date-time format rather than converting
to text. In your original formula, use =C2-B2 and format for time as desired (Format -
Cells - Number tab). Time calculations are straightfoward done that way. Well, mostly.
You have to change the formatting in many cases.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"m greenly" <m wrote in message
...
i have times listed as "h:mm" and i would like to show the average. note that
the times are calculated, not entered manually.
formula in time row: =TEXT(C2-B2, "h:mm")

what formula would i put in the cell where i want the average of all times?
thanks for your help.

average of times
 

thanks earl

i went with the simplified version, no need to make things more complicated
than they are.

another question would be how can i add additional rows with the average
adjusting itself?

"Earl Kiosterud" wrote:

M,

If these formulas were in D2:D4, you could use =AVERAGE(TIMEVALUE(D2:D4)). This is an array
formula, and must be commited to the cell with Ctrl-Shift-Enter, not just Enter.

It might be simpler to just keep your times in true date-time format rather than converting
to text. In your original formula, use =C2-B2 and format for time as desired (Format -
Cells - Number tab). Time calculations are straightfoward done that way. Well, mostly.
You have to change the formatting in many cases.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"m greenly" <m wrote in message
...
i have times listed as "h:mm" and i would like to show the average. note that
the times are calculated, not entered manually.
formula in time row: =TEXT(C2-B2, "h:mm")

what formula would i put in the cell where i want the average of all times?
thanks for your help.

average of times
 

M,

If by "simplified" you mean my second solution (=C2-B2), the average formula can go to the
bottome of the worksheet without untoward results. It ignores empty cells.
=AVERAGE(D2:D65536)

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"m greenly" wrote in message
...
thanks earl

i went with the simplified version, no need to make things more complicated
than they are.

another question would be how can i add additional rows with the average
adjusting itself?

"Earl Kiosterud" wrote:

M,

If these formulas were in D2:D4, you could use =AVERAGE(TIMEVALUE(D2:D4)). This is an
array
formula, and must be commited to the cell with Ctrl-Shift-Enter, not just Enter.

It might be simpler to just keep your times in true date-time format rather than
converting
to text. In your original formula, use =C2-B2 and format for time as desired (Format -
Cells - Number tab). Time calculations are straightfoward done that way. Well, mostly.
You have to change the formatting in many cases.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"m greenly" <m wrote in message
...
i have times listed as "h:mm" and i would like to show the average. note that
the times are calculated, not entered manually.
formula in time row: =TEXT(C2-B2, "h:mm")

what formula would i put in the cell where i want the average of all times?
thanks for your help.

average of times
 

Earl,

yes, i meant the second solution (=C2-B2). Thanks, you're awesome.

"Earl Kiosterud" wrote:

M,

If by "simplified" you mean my second solution (=C2-B2), the average formula can go to the
bottome of the worksheet without untoward results. It ignores empty cells.
=AVERAGE(D2:D65536)

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"m greenly" wrote in message
...
thanks earl

i went with the simplified version, no need to make things more complicated
than they are.

another question would be how can i add additional rows with the average
adjusting itself?

"Earl Kiosterud" wrote:

M,

If these formulas were in D2:D4, you could use =AVERAGE(TIMEVALUE(D2:D4)). This is an
array
formula, and must be commited to the cell with Ctrl-Shift-Enter, not just Enter.

It might be simpler to just keep your times in true date-time format rather than
converting
to text. In your original formula, use =C2-B2 and format for time as desired (Format -
Cells - Number tab). Time calculations are straightfoward done that way. Well, mostly.
You have to change the formatting in many cases.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"m greenly" <m wrote in message
...
i have times listed as "h:mm" and i would like to show the average. note that
the times are calculated, not entered manually.
formula in time row: =TEXT(C2-B2, "h:mm")

what formula would i put in the cell where i want the average of all times?
thanks for your help.