Calculating average based on different amounts of blank cells

[Elrond]

Hello,
I have a table of moths captured in a trap every few days.
I want to make a chart that would show me the daily average.
Note that the amount of days between each capture varies.
See the example table below, I am looking for a formula that could give me the the third column ("average moths per day").

Any ideas?

Thanks a lot!
E.

[TABLE="class: grid, width: 265, align: left"]
<tbody>[TR]
[TD="align: left"]date[/TD]
[TD="align: left"]moths in trap[/TD]
[TD="align: left"]average moths per day[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2016[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2016[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]17/08/2016[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]18/08/2016[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]19/08/2016[/TD]
[TD]40[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2016[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD="align: right"]21/08/2016[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD="align: right"]22/08/2016[/TD]
[TD]60[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD="align: right"]23/08/2016[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2016[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]25/08/2016[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]26/08/2016[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]27/08/2016[/TD]
[TD]20[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sinon]

I came up with something but have not tested thoroughly. It does seem to work with your example. Assuming your Date is in Column A and your trapped moths is in column B, add the following array formula in Column C:

=IF(B2>0,B2/(ROW(B2)-MAX(IF($B$1:B1>0,ROW($B$1:B1)),0)),C3)

Confirm with CTRL+Shift+Enter (this will add curly braces at the beginning and end of the formula) and apply below

 

[Elrond]

Thank you Simon,
It seems to work. The only problem is when trapped moths are 0.
In the example below the 15/8 sholdn't be counted, so that the 8 should be a 10.

[TABLE="class: outer_border, width: 327, align: left"]
<tbody>[TR]
[TD="align: center"]date[/TD]
[TD="align: center"]moths in trap[/TD]
[TD="align: center"]average moths per day - formula[/TD]
[/TR]
[TR]
[TD="align: center"]15/08/2016[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]16/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]17/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]18/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]19/08/2016[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]20/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]21/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]22/08/2016[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]23/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]24/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]25/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]26/08/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]27/08/2016[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sinon]

Try this in C2:

=IF(AND(ISNUMBER(B2),B2=0),0,IF(ROW(B2)=2,B2,IF(B2<>0,B2/(ROW(B2)-MAX(IF(ISNUMBER($B$1:B1),IF($B$1:B1>=0,ROW($B$1:B1)),0))),C3)))

Confirm with CTRL+Shift+Enter

 

[Elrond]

Brilliant, thanks a lot!