How to average days, hours and minutes excluding "NULL" values

[Carsinojen]

I am looking to calculate the average of cells formatted like this "10d:0h:0m" and exclude "NULL" values from the average. I would rather not convert the d, h, m to hours if possible.

 

[Eric W]

When you have a custom format like that, you need to create your own routines to pick apart the pieces. Here's one way:

Excel 2010

	A	B	C
10d:0h:0m
7d:12h:12m
11d:1h:59m
5d:21h:17m
8d:14h:52m

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]49648[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12412[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=INT(C5/COUNTA(A1:A5))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=INT(C6/1440)&"d:"&INT((C6-INT(C6/1440)*1440)/60)&"h:"&MOD(C6,60)&"m"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=SUM(IF(A1:A5="",0,LEFT(A1:A5,FIND("d",A1:A5)-1)*1440+MID(A1:A5,FIND(":",A1:A5)+1,FIND("h",A1:A5)-FIND(":",A1:A5)-1)*60+MID(A1:A5,FIND("h",A1:A5)+2,FIND("m",A1:A5)-FIND("h",A1:A5)-2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I did it with 3 cells. It's pretty much required that you convert the pieces to minutes in some form. In theory, you could combine the 3 cells into 1 huge function, but I think the 3 here are long enough. Also note that the C5 equation is HIGHLY dependent on your data being entered in the exact format you showed, a number, "d:", another number, "h:", another number, and "m".

Let me know if this helps.

 

[Carsinojen]

Thank you so much! Brilliant...

 

[Eric W]

You're welcome.