Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I average time (in text format)
I have a big problem.
I have calculated multiple time values using the following example: =TEXT(B2-B1,"h:mm") where B2 and B1 are different time values I get an answer like 0:16 (indicating 0hr and 16min) and 1:14 for example.. Here is my question. I have tried to average the answers (like the average of 0:16 and 1:14) and I get #DIV/0 returned. It seems that I cannot use theAVERAGE function to perform this because it does not recognize the values 0:16 and 1:14. I think it is because they are in TEXT format. Is this correct? If so is there any way that I can fix this? Because I have multiple values over multiple worksheets and would like to average them all. It would be extremely difficult and time consuming to use the VALUE function to change all of the answers because I would have to do this to each answer individually. A second question is, does anyone know how to apply the VALUE function over multiple sheets. Thanks so much |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I average time (in text format)
Maybe...
=b2-b1 but use a custom format of h:mm or =--TEXT(B2-B1,"h:mm") and use a custom format of h:mm Butterycolor wrote: I have a big problem. I have calculated multiple time values using the following example: =TEXT(B2-B1,"h:mm") where B2 and B1 are different time values I get an answer like 0:16 (indicating 0hr and 16min) and 1:14 for example.. Here is my question. I have tried to average the answers (like the average of 0:16 and 1:14) and I get #DIV/0 returned. It seems that I cannot use theAVERAGE function to perform this because it does not recognize the values 0:16 and 1:14. I think it is because they are in TEXT format. Is this correct? If so is there any way that I can fix this? Because I have multiple values over multiple worksheets and would like to average them all. It would be extremely difficult and time consuming to use the VALUE function to change all of the answers because I would have to do this to each answer individually. A second question is, does anyone know how to apply the VALUE function over multiple sheets. Thanks so much -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing time and text format | Excel Worksheet Functions | |||
Time Diff from text format | Excel Worksheet Functions | |||
Average Time Format | Excel Discussion (Misc queries) | |||
How do I convert a time to text format. | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions |