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:

C/R	D	E	F
6	Sample A	Sample B	Sample C
7	03/01/19 09:07:44	03/01/19 08:22:14	03/01/19 09:22:25
8	03/02/19 08:41:13	03/02/19 07:55:52	03/02/19 09:16:33
9	03/04/19 09:10:29	03/04/19 08:33:02	03/04/19 09:09:34
10	03/06/19 08:55:00	03/06/19 08:11:26	03/06/19 08:55:58
11	03/07/19 09:12:04	03/07/19 07:51:56	03/07/19 09:29:34
12	9:01a	8:10a	9:14a

<tbody>
</tbody>

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.

C/R	D	E	F
6	Sample A	Sample B	Sample C
7	03/01/19 09:07:44	03/01/19 08:22:14	03/01/19 09:22:25
8	03/02/19 08:41:13	03/02/19 07:55:52	03/02/19 09:16:33
9	03/04/19 09:10:29		03/04/19 09:09:34
10	03/06/19 08:55:00	03/06/19 08:11:26	03/06/19 08:55:58
11	03/07/19 09:12:04	03/07/19 07:51:56	03/07/19 09:29:34
12	9:01a	6:28a	9:14a

<tbody>
</tbody>

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 ... !!!