| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to average hh:mm:ss when hh is 00
I' trying to get the average of elapsed times like 00:01:22, 00:08:23,
00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do this and keep the hh:mm:ss format? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to average hh:mm:ss when hh is 00
This suggests the data is not true time values because =AVERAGE(A1:A5) works
when the range holds time values. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "the mechanic" wrote in message ... I' trying to get the average of elapsed times like 00:01:22, 00:08:23, 00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do this and keep the hh:mm:ss format? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to average hh:mm:ss when hh is 00
It only works if I allow Excel to show the time in the formula bar as
12:01:22 AM, 12:08:23 AM, 12:10:59 AM. These times are elpased time values, not time of day. As the developer of the timesheet, I might understand, but my users may be baffled as to why a column heading of "MAXIMUM TIME" has "AM" and "PM" in the formula. "Bernard Liengme" wrote: This suggests the data is not true time values because =AVERAGE(A1:A5) works when the range holds time values. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "the mechanic" wrote in message ... I' trying to get the average of elapsed times like 00:01:22, 00:08:23, 00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do this and keep the hh:mm:ss format? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to average hh:mm:ss when hh is 00
Assuming your textual representations of time are in column D, starting with
row 1. Formula is: =TEXT(SUMPRODUCT(--(D1:D100))/COUNTA(D1:D100),"[hh]:mm:ss") -- John C "the mechanic" wrote: It only works if I allow Excel to show the time in the formula bar as 12:01:22 AM, 12:08:23 AM, 12:10:59 AM. These times are elpased time values, not time of day. As the developer of the timesheet, I might understand, but my users may be baffled as to why a column heading of "MAXIMUM TIME" has "AM" and "PM" in the formula. "Bernard Liengme" wrote: This suggests the data is not true time values because =AVERAGE(A1:A5) works when the range holds time values. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "the mechanic" wrote in message ... I' trying to get the average of elapsed times like 00:01:22, 00:08:23, 00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do this and keep the hh:mm:ss format? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to average hh:mm:ss when hh is 00
Nice work John,
thanks "John C" wrote: Assuming your textual representations of time are in column D, starting with row 1. Formula is: =TEXT(SUMPRODUCT(--(D1:D100))/COUNTA(D1:D100),"[hh]:mm:ss") -- John C "the mechanic" wrote: It only works if I allow Excel to show the time in the formula bar as 12:01:22 AM, 12:08:23 AM, 12:10:59 AM. These times are elpased time values, not time of day. As the developer of the timesheet, I might understand, but my users may be baffled as to why a column heading of "MAXIMUM TIME" has "AM" and "PM" in the formula. "Bernard Liengme" wrote: This suggests the data is not true time values because =AVERAGE(A1:A5) works when the range holds time values. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "the mechanic" wrote in message ... I' trying to get the average of elapsed times like 00:01:22, 00:08:23, 00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do this and keep the hh:mm:ss format? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Linear Mode
