averaging times

[SYBS]

Hi,

I have a problem with averaging times.

This is what I have

e12 – i12 are input as the time taken to achieve a task. This could be
anything from 0.01 (min.secs) up to 9.59. These times are then averaged to
return a value in P12. I am struggled to validate the type of entry to go
into the five time boxes, point 1. I don’t seem to be able to validate using
time and if the times are entered as 1.59, 147, etc , point 2, when they are
averaged they average to 100/ths of a second, in other words instead of
recognising as time it treats as a decimal (up to 100 ths instead of 60 ths).
I really am at a loss here. Anyone help please.

Thanks

Sybs

 

[Miguel Zapico]

You may transform the decimal entry to minutes and seconds with a formula
like this:
=TIME(0,INT(A1),MOD(A1,1)*100)
This will split the number on the decimal point, and add the integer part to
the minutes and the decimal part to the seconds.

Hope this helps,
Miguel.

 

[SYBS]

Thanks, that works fine for one number conversion. Is there a way for it to
display purely as min.secs (1.45) when the result has been averaged, and
although I have put this into my spreadsheet, and it does work great, I am
struggling with how to combine it with an average,( an average of three
columns A,B,C all entered in the same format and all needing to be converted
to time and then the result showing in col D.

Hope that makes sense.

Sybs

 

[Miguel Zapico]

For the first question, one way of formatting back the number can be a
formula like:
=MINUTE(A1)+SECOND(A1)/100
About how to combine with the average, you have different degrees on
complexity depending on the number of extra rows/columns that you want to use.
For a first approach, you can add a row with the TIME formula, average over
that row, and apply this new formula to the result.
An intermediate solution is make the transformation at the same time as the
average, with an array formula like:
=AVERAGE(TIME(0,INT(A2:C2),MOD(A2:C2,1)*100))
Enter it with Ctrl+Shift+Enter on column D. It will show the information in
the time format, so you may need an additional column to tranform the data to
the #.## format.
The final one is combine the two formulas in one single array formula, like:
=MINUTE(AVERAGE(TIME(0,INT(A2:C2),MOD(A2:C2,1)*100)))+SECOND(AVERAGE(TIME(0,INT(A2:C2),MOD(A2:C2,1)*100)))/100
It looks more complex, but you don't need extra columns/rows.

Hope this helps,
Miguel

 

[SYBS]

Thank you so much, works brilliantly. Been away a while but have got back
to it and joy of joys Much Thanks