Posted to microsoft.public.excel.misc
|
|
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.
|