Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging times
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 dont 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging times
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" wrote: 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 dont 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging times
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" wrote: 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" wrote: 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 dont 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging times
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" wrote: 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" wrote: 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" wrote: 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 dont 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging times
Thank you so much, works brilliantly. Been away a while but have got back
to it and joy of joys Much Thanks "Miguel Zapico" wrote: 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" wrote: 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" wrote: 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" wrote: 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 dont 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting multiple minimum times | Excel Discussion (Misc queries) | |||
Averaging Times w/ AM PM | Excel Discussion (Misc queries) | |||
Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) | |||
Formula for averaging times | Excel Discussion (Misc queries) | |||
SUMPRODUCT ON TIMES | Excel Worksheet Functions |