# Rolling Average Ignoring Blanks

#### mdshields

##### New Member
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
IJKLM
3Baseline =117%
4DateStd HrsClock HrsProductivity3 Day Avg
526-May85.841.2578%
627-May5557-18%
728-May11354.7576%41.8%
829-May3533-9%19.9%
930-May1524.25-47%24.4%
1031-May -25.4%
111-Jun5540.516%-7.6%
122-Jun5045-5%5.0%
Bench
Cell Formulas
RangeFormula
L5:L12L5=IF(AND(K5=0,J5>0),\$J\$3,IFERROR(((J5/K5)-\$J\$3)/\$J\$3,""))
M7:M12M7=((SUM(J5:J7)/SUM(K5:K7))-\$J\$3)/\$J\$3
Press CTRL+SHIFT+ENTER to enter array formulas.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### jasonb75

##### Well-known Member
This looks right, but messy.
Book2
IJKLM
3Baseline =1.17
4DateStd HrsClock HrsProductivity3 Day Avg
526/05/202085.841.250.777777778
627/05/20205557-0.175288649
728/05/202011354.750.764040120.417798
829/05/20203533-0.0934990930.198648
930/05/20201524.25-0.4713190590.243895
1031/05/2020 0.243895
1101/06/20205540.50.160704864-0.08191
1202/06/20205045-0.050332384-0.06548
Sheet3
Cell Formulas
RangeFormula
L5:L12L5=IF(AND(K5=0,J5>0),\$J\$3,IFERROR(((J5/K5)-\$J\$3)/\$J\$3,""))
M5:M12M5=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

##### New Member
This looks right, but messy.
Book2
IJKLM
3Baseline =1.17
4DateStd HrsClock HrsProductivity3 Day Avg
526/05/202085.841.250.777777778
627/05/20205557-0.175288649
728/05/202011354.750.764040120.417798
829/05/20203533-0.0934990930.198648
930/05/20201524.25-0.4713190590.243895
1031/05/2020 0.243895
1101/06/20205540.50.160704864-0.08191
1202/06/20205045-0.050332384-0.06548
Sheet3
Cell Formulas
RangeFormula
L5:L12L5=IF(AND(K5=0,J5>0),\$J\$3,IFERROR(((J5/K5)-\$J\$3)/\$J\$3,""))
M5:M12M5=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.

Replies
10
Views
95
Replies
14
Views
724
Replies
4
Views
4K
Replies
1
Views
32
Replies
2
Views
180