How to convert minutes to seconds

[Jane D]

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

 

[Ragdyer]

If those entries were *legal* XL times, use:

=Average(B1:E1)

However, by the looks of your data, I doubt it!

Easiest way is to enter *true* XL times in cells formatted to Time:
00:02:40
You could custom format to use the decimal for the seconds:
00:02.40

In that way you can calculate with math functions.

If your data is really plain, keyed in numbers, you'll have to perform more
complicated procedures to get what you're looking for.

SO ... which is it?
What type of values do you have in your sheet?

 

[JoeU2004]

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 -----

 

[Jacob Skaria]

Hi Jane

-- If 2 hours 30 minutes is recorded as 2.3 to convert in into seconds use
the formula =INT(A1)*60+MOD(A1,1)*100

-- If 2 hours 30 minutes is recorded as 2.5 to find an average just use the
AVERAGE() function.

PS: It is always easy to handle time if the data is in excel time format..

If this post helps click Yes

 

[Shane Devenshire]

Hi,

If you must keep the entries in this form,

If 2.5 means 2 minutes and 30 seconds then

=A1*60 will convert this to 150 seconds

If 2.5 means 2 minutes and 50 seconds then

=60*INT(A1)+100*MOD(A1,1)

 

[Rick Rothstein]

How are you preserving that trailing 0 in your 2.40 entry? Is the entry
formatted as Text, or is it a number with a Custom Format?