average of times

[m greenly]

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.

 

[Earl Kiosterud]

M,

If these formulas were in D24, you could use =AVERAGE(TIMEVALUE(D24)). 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.

 

[m greenly]

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]

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(D265536)

 

[m greenly]

Earl,

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