Counting back from today but ignoring some cells

[Bren4242]

Hi All,
I input a range of data for each day of the year. I need to calculate the average output over the last 30 "production days" and just cannot get it right.

1) I can't take the usual average as some days are not production days, so I need to exclude those days from the calculation.
2) I need the average over the last 30 production days, so those non-production days should be ignored in the 30-day count
3) There are some days where production is also zero, but it's still a production day, so that day and the zero has to be included in the average.

Some examples below:

Date (dd/mm/yyyy)	Production day?	Production
01/01/2022	Yes	0
02/01/2022	No	0
03/01/2022	No	0
...inbetween dates	...assume all "Yes"	...assume all zero
30/1/2022	Yes	0
31/1/2022	Yes	7
01/02/2022	Yes	8

for 01/02/2022: Average is calculated for the date range 01/01/2022-01/02/2022, 32 days but two of those are non-production days. Average = (7+8)/30 = 0.5

 

[Snakehips]

@Bren4242 Welcome.
Are you wanting the 30 day average on a daily basis?
Does like below help? It utilisesa 'Helper' column that you could hide if you wish.

Book1
	A	B	C	D	E
1	Date	Prod Day	Prod	30 Day Av	Helper
2	01/01/2022	Yes	0	0	1
3	02/01/2022	No	0	0
4	03/01/2022	No	0	0
5	04/01/2022	Yes	0	0	2
6	05/01/2022	Yes	0	0	3
7	06/01/2022	Yes	0	0	4
8	07/01/2022	Yes	0	0	5
9	08/01/2022	Yes	0	0	6
29	28/01/2022	Yes	0	0	26
30	29/01/2022	Yes	0	0	27
31	30/01/2022	Yes	0	0	28
32	31/01/2022	Yes	7	0.24	29
33	01/02/2022	Yes	8	0.5	30
34	02/02/2022	No	0	0
35	03/02/2022	No	0	0
36	04/02/2022	Yes	15	1	31
37	05/02/2022	No	0	0
38	06/02/2022	Yes	0	1	32
39	07/02/2022	Yes	0	1	33
40	08/02/2022	Yes	0	1	34
41
Sheet4

Cell Formulas
Range	Formula
D29:D40,D2:D9	D2	=IFERROR(AVERAGEIFS(C$2:C2,B$2:B2,"Yes",E$2:E2,"<"&E2+1,E$2:E2,">"&E2-30),L1)
E29:E40,E2:E9	E2	=IF(B2="Yes",COUNTIF(B$2:B2,"Yes"),"")

 

[Bren4242]

@Snakehips , that solution works perfectly thanks.