Calculating average times in expandable table

[JenniferMurphy]

I have a table with three sets of timestamps when some activity took place. I want to calculate the average of the time portion of the timestamps. I was able to get an array formula using the MOD function to work:

[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD="align: center"]C/R[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Sample A[/TD]
[TD="align: center"]Sample B[/TD]
[TD="align: center"]Sample C[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]03/01/19 09:07:44[/TD]
[TD="align: center"]03/01/19 08:22:14[/TD]
[TD="align: center"]03/01/19 09:22:25[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]03/02/19 08:41:13[/TD]
[TD="align: center"]03/02/19 07:55:52[/TD]
[TD="align: center"]03/02/19 09:16:33[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]03/04/19 09:10:29[/TD]
[TD="align: center"]03/04/19 08:33:02[/TD]
[TD="align: center"]03/04/19 09:09:34[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]03/06/19 08:55:00[/TD]
[TD="align: center"]03/06/19 08:11:26[/TD]
[TD="align: center"]03/06/19 08:55:58[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]03/07/19 09:12:04[/TD]
[TD="align: center"]03/07/19 07:51:56[/TD]
[TD="align: center"]03/07/19 09:29:34[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]9:01a[/TD]
[TD="align: center"]8:10a[/TD]
[TD="align: center"]9:14a[/TD]
[/TR]
</tbody>[/TABLE]

The formula in D12 is

{=AVERAGE(MOD(OFFSET(F6,1,0):OFFSET(F12,-1,0),1))}

The problem arises if there is missing data. The AVERAGE function will ignore empty cells, but the MOD function turns an empty cell into a zero, messing up the average.

[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD="align: center"]C/R[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Sample A[/TD]
[TD="align: center"]Sample B[/TD]
[TD="align: center"]Sample C[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]03/01/19 09:07:44[/TD]
[TD="align: center"]03/01/19 08:22:14[/TD]
[TD="align: center"]03/01/19 09:22:25[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]03/02/19 08:41:13[/TD]
[TD="align: center"]03/02/19 07:55:52[/TD]
[TD="align: center"]03/02/19 09:16:33[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]03/04/19 09:10:29[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03/04/19 09:09:34[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]03/06/19 08:55:00[/TD]
[TD="align: center"]03/06/19 08:11:26[/TD]
[TD="align: center"]03/06/19 08:55:58[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]03/07/19 09:12:04[/TD]
[TD="align: center"]03/07/19 07:51:56[/TD]
[TD="align: center"]03/07/19 09:29:34[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]9:01a[/TD]
[TD="align: center"]6:28a[/TD]
[TD="align: center"]9:14a[/TD]
[/TR]
</tbody>[/TABLE]

I tried putting a text string in that cell ('n/a or '--), but that gets a value error. Is there a way to get this work calculate the correct average of the non-empty cells?

 

[James006]

Hello,

If I understand correctly in cell D2 ... the formula works out the Average for Column F ... (which can be disturbing ...)

But the issue remains to avoid blank cells ...

You could test following:

=SUM(MOD(OFFSET(F6,1,0):OFFSET(F12,-1,0),1))/(COUNTA(OFFSET(F6,1,0):OFFSET(F12,-1,0)))

Hope this will help

 

[JenniferMurphy]

Hello,

If I understand correctly in cell D2 ... the formula works out the Average for Column F ... (which can be disturbing ...)

Doh! I meant to say that formula was in F12, no D12. Sorry about that.

But the issue remains to avoid blank cells ...

You could test following:

=SUM(MOD(OFFSET(F6,1,0):OFFSET(F12,-1,0),1))/(COUNTA(OFFSET(F6,1,0):OFFSET(F12,-1,0)))

Hope this will help

Brilliant. It works perfectly, provided that I make it an array formula:

{=SUM(MOD(OFFSET(F6,1,0):OFFSET(F12,-1,0),1))/(COUNTA(OFFSET(F6,1,0):OFFSET(F12,-1,0)))}

Thank you!

 

[James006]

Glad you could fix your problem ...:wink:

Thanks a lot for the Beer ... !!!