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.

date	moths in trap	average moths per day
15/08/2016	1	1
16/08/2016		10
17/08/2016		10
18/08/2016		10
19/08/2016	40	10
20/08/2016		20
21/08/2016		20
22/08/2016	60	20
23/08/2016		4
24/08/2016		4
25/08/2016		4
26/08/2016		4
27/08/2016	20	4

<tbody>
</tbody>

 

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

date	moths in trap	average moths per day - formula
15/08/2016	0	8
16/08/2016		8
17/08/2016		8
18/08/2016		8
19/08/2016	40	8
20/08/2016		20
21/08/2016		20
22/08/2016	60	20
23/08/2016		4
24/08/2016		4
25/08/2016		4
26/08/2016		4
27/08/2016	20	4

<tbody>
</tbody>

 

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