Rolling Average Ignoring Blanks

[mdshields]

Hello,

I'd like to find the 3 day rolling average of two columns while ignoring blanks. Looking at M7 it would be something similar to that formula but ignoring blanks in J and K while automatically finding the previous 3 non blank values. I've tried using offset, large, and various if statements but I keep confusing myself and would appreciate the help.

Thanks!

Bench PA2 PA9 Rolling Average.xlsx
	I	J	K	L	M
3	Baseline =	117%
4	Date	Std Hrs	Clock Hrs	Productivity	3 Day Avg
5	26-May	85.8	41.25	78%
6	27-May	55	57	-18%
7	28-May	113	54.75	76%	41.8%
8	29-May	35	33	-9%	19.9%
9	30-May	15	24.25	-47%	24.4%
10	31-May				-25.4%
11	1-Jun	55	40.5	16%	-7.6%
12	2-Jun	50	45	-5%	5.0%
Bench

Cell Formulas
Range	Formula
L5:L12	L5	=IF(AND(K5=0,J5>0),$J$3,IFERROR(((J5/K5)-$J$3)/$J$3,""))
M7:M12	M7	=((SUM(J5:J7)/SUM(K5:K7))-$J$3)/$J$3
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

This looks right, but messy.

Book2
	I	J	K	L	M
3	Baseline =	1.17
4	Date	Std Hrs	Clock Hrs	Productivity	3 Day Avg
5	26/05/2020	85.8	41.25	0.777777778
6	27/05/2020	55	57	-0.175288649
7	28/05/2020	113	54.75	0.76404012	0.417798
8	29/05/2020	35	33	-0.093499093	0.198648
9	30/05/2020	15	24.25	-0.471319059	0.243895
10	31/05/2020				0.243895
11	01/06/2020	55	40.5	0.160704864	-0.08191
12	02/06/2020	50	45	-0.050332384	-0.06548
Sheet3

Cell Formulas
Range	Formula
L5:L12	L5	=IF(AND(K5=0,J5>0),$J$3,IFERROR(((J5/K5)-$J$3)/$J$3,""))
M5:M12	M5	=IFERROR((SUM(IF(I$5:I5=AGGREGATE(14,6,I$5:I5/ISNUMBER(J$5:J5)/ISNUMBER(K$5:K5),{1,2,3}),J$5:J5))/SUM(IF(I$5:I5=AGGREGATE(14,6,I$5:I5/ISNUMBER(J$5:J5)/ISNUMBER(K$5:K5),{1,2,3}),K$5:K5))-$J$3)/$J$3,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mdshields]

This looks right, but messy.

Book2
	I	J	K	L	M
3	Baseline =	1.17
4	Date	Std Hrs	Clock Hrs	Productivity	3 Day Avg
5	26/05/2020	85.8	41.25	0.777777778
6	27/05/2020	55	57	-0.175288649
7	28/05/2020	113	54.75	0.76404012	0.417798
8	29/05/2020	35	33	-0.093499093	0.198648
9	30/05/2020	15	24.25	-0.471319059	0.243895
10	31/05/2020				0.243895
11	01/06/2020	55	40.5	0.160704864	-0.08191
12	02/06/2020	50	45	-0.050332384	-0.06548
Sheet3

Cell Formulas
Range	Formula
L5:L12	L5	=IF(AND(K5=0,J5>0),$J$3,IFERROR(((J5/K5)-$J$3)/$J$3,""))
M5:M12	M5	=IFERROR((SUM(IF(I$5:I5=AGGREGATE(14,6,I$5:I5/ISNUMBER(J$5:J5)/ISNUMBER(K$5:K5),{1,2,3}),J$5:J5))/SUM(IF(I$5:I5=AGGREGATE(14,6,I$5:I5/ISNUMBER(J$5:J5)/ISNUMBER(K$5:K5),{1,2,3}),K$5:K5))-$J$3)/$J$3,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Works great! Didn't know aggregate had so many functions, thanks.