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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,621
Office Version
2019
Platform
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,111
Messages
5,509,269
Members
408,720
Latest member
mohamed nageeb

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top