ExcelBanter - View Single Post

joeu2004

"Jane D" <Jane wrote:
I have a time that is documented in minutes and seconds ie 2.40

Why do you use that form instead of the standard form mm:ss (colon instead
of period)?

You would enter the data in the form 0:mm:ss, but format the cells with the
Custom format [m]:ss.

Then the average could be computed straight-forwardly, e.g. AVERAGE(B1:E1)
with the Custom format [m]:ss.000 or [m]:ss. Use the latter Custom format
to round the __displayed__ value to seconds. Use the following expression
to round the __actual__ value to seconds:

--TEXT(AVERAGE(B1:E1),"h:mm:ss")

with the Custom format [m]:ss.

I believe I will need to convert these to seconds, to enable me to
obtain an average

Or convert to minutes and fractions of minutes. For example:

=--TEXT(SUMPRODUCT(INT(B1:E1)+100*MOD(B1:E1,1)/60)/COUNT(B1:E1)/1440,"h:mm:ss.000")

with the Custom format [m]:ss.000 or [m]:ss. Use the latter Custom format
to round the __displayed__ value to seconds. Use the TEXT format "h:mm:ss"
to round the __actual__ value to seconds.

----- original message -----

"Jane D" <Jane wrote in message
...
Hi,

I'm still new to formula's however seem to be finding my way around the
basics so please not too much jargon if you know the answer to my
question -
thank you.

I have a time that is documented in minutes and seconds ie 2.40 however I
believe I will need to convert these to seconds, to enable me to obtain an
average - is this correct or can anyone help me.

COL A COL B COL C COL D COL E
Average 2.40 2.14 2.32 2.52

Thanks