Rolling Average Ignoring Blanks

mdshields

New Member
Joined
Jun 30, 2016
Messages
33
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.
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
10,836
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 30, 2016
Messages
33
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,622
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top