Moving average in table with non-numeric data

Reboshua

Board Regular
Joined
Jan 23, 2015
Messages
88
Need some expert help, please. As you can see below, the formula in cell c13 is an array that calculates a 12-day moving average for that data in column B. You can also see that column B has non-numeric data, which is why I'm using the array in C.

Here's my problem. I want the formula in column C to be part of the table so that it (and its associated charts on other tabs) auto-update when I add the next day's worth of data.

Any ideas? (Right now, the formula in C13 is just copied down, the table isn't automatically filling in that formula for me, presumably because I didn't create the formula in C2 (because it's a 12-day moving average).

Happy to clarify more...and thank you in advance for your help.

Unknown
ABC
1Date2 ARI ARI2
242099#DIV/0!
342100 6.91
442101 14.86
542102 4.24
642103#DIV/0!
742104 7.45
842105 10.65
942106 6.24
1042107 14.86
1142108 3.84
1242109 7.68
1342110 15.99 9.27
1442111 17.14 9.99
1542112 5.34 9.84
1642113 10.19 9.42
1742114#DIV/0! 9.94
1842115 3.84 9.38
1942116 12.95 9.88
2042117#DIV/0! 9.81

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
A2=TEXT(TeamMA!$A2,"0000\/00\/00")+0
B2=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A2,'DataByDate-H'!$H:$H,B$1)
A3=TEXT(TeamMA!$A3,"0000\/00\/00")+0
B3=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A3,'DataByDate-H'!$H:$H,B$1)
A4=TEXT(TeamMA!$A4,"0000\/00\/00")+0
B4=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A4,'DataByDate-H'!$H:$H,B$1)
A5=TEXT(TeamMA!$A5,"0000\/00\/00")+0
B5=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A5,'DataByDate-H'!$H:$H,B$1)
A6=TEXT(TeamMA!$A6,"0000\/00\/00")+0
B6=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A6,'DataByDate-H'!$H:$H,B$1)
A7=TEXT(TeamMA!$A7,"0000\/00\/00")+0
B7=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A7,'DataByDate-H'!$H:$H,B$1)
A8=TEXT(TeamMA!$A8,"0000\/00\/00")+0
B8=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A8,'DataByDate-H'!$H:$H,B$1)
A9=TEXT(TeamMA!$A9,"0000\/00\/00")+0
B9=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A9,'DataByDate-H'!$H:$H,B$1)
A10=TEXT(TeamMA!$A10,"0000\/00\/00")+0
B10=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A10,'DataByDate-H'!$H:$H,B$1)
A11=TEXT(TeamMA!$A11,"0000\/00\/00")+0
B11=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A11,'DataByDate-H'!$H:$H,B$1)
A12=TEXT(TeamMA!$A12,"0000\/00\/00")+0
B12=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A12,'DataByDate-H'!$H:$H,B$1)
A13=TEXT(TeamMA!$A13,"0000\/00\/00")+0
B13=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A13,'DataByDate-H'!$H:$H,B$1)
A14=TEXT(TeamMA!$A14,"0000\/00\/00")+0
B14=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A14,'DataByDate-H'!$H:$H,B$1)
A15=TEXT(TeamMA!$A15,"0000\/00\/00")+0
B15=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A15,'DataByDate-H'!$H:$H,B$1)
A16=TEXT(TeamMA!$A16,"0000\/00\/00")+0
B16=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A16,'DataByDate-H'!$H:$H,B$1)
A17=TEXT(TeamMA!$A17,"0000\/00\/00")+0
B17=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A17,'DataByDate-H'!$H:$H,B$1)
A18=TEXT(TeamMA!$A18,"0000\/00\/00")+0
B18=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A18,'DataByDate-H'!$H:$H,B$1)
A19=TEXT(TeamMA!$A19,"0000\/00\/00")+0
B19=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A19,'DataByDate-H'!$H:$H,B$1)
A20=TEXT(TeamMA!$A20,"0000\/00\/00")+0
B20=AVERAGEIFS('DataByDate-H'!$AJ:$AJ,'DataByDate-H'!$G:$G,TeamMA!$A20,'DataByDate-H'!$H:$H,B$1)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
C13{=AVERAGE(IF(ISNUMBER(B2:B13),B2:B13))}
C14{=AVERAGE(IF(ISNUMBER(B3:B14),B3:B14))}
C15{=AVERAGE(IF(ISNUMBER(B4:B15),B4:B15))}
C16{=AVERAGE(IF(ISNUMBER(B5:B16),B5:B16))}
C17{=AVERAGE(IF(ISNUMBER(B6:B17),B6:B17))}
C18{=AVERAGE(IF(ISNUMBER(B7:B18),B7:B18))}
C19{=AVERAGE(IF(ISNUMBER(B8:B19),B8:B19))}
C20{=AVERAGE(IF(ISNUMBER(B9:B20),B9:B20))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
1st I would fix the formula that is generating the error, maybe something like =if(A2="","",your-formula)
2nd, try this instead of that array formula...
C2=IF(OR(A2="",COUNTA($A$2:A2)<12),"",AVERAGEIFS(B:B,A:A,"<="&A2,A:A,">="&A2-$D$1))
copied down way past your data
 
Upvote 0
I want the formula in column C to be part of the table so that it (and its associated charts on other tabs) auto-update when I add the next day's worth of data.

Any ideas? (Right now, the formula in C13 is just copied down, the table isn't automatically filling in that formula for me, presumably because I didn't create the formula in C2 (because it's a 12-day moving average).
Posting tip: When using one of the HTML makers to post a section of your sheet, look for options to limit what formulas are displayed - like mine below. Doing so means that you are not filling up large amounts of space with repeated formulas which make your post and the thread harder to read & navigate.

Anyway, try this in cell C2 of your table. It should allow the flash fill and auto-fill as you extend the table.

Excel Workbook
ABC
1Date2ARIARI2
242099#DIV/0! 
3421006.91
44210114.86
5421024.24
642103#DIV/0!
7421047.45
84210510.65
9421066.24
104210714.86
11421083.84
12421097.68
134211015.999.27
144211117.149.99
15421125.349.84
164211310.199.42
1742114#DIV/0!9.94
18421153.849.38
194211612.959.88
2042117#DIV/0!9.81
21
Moving average
 
Upvote 0
Peter, do you know if there is any advantage to be gained by using ROWS() instead of COUNTA() (just curious, I have always used COUNTA(), never thought really to use ROWS() )
 
Upvote 0
Peter, do you know if there is any advantage to be gained by using ROWS() instead of COUNTA() (just curious, I have always used COUNTA(), never thought really to use ROWS() )
Hi Ford
I can think of 3 reasons for using ROWS() instead of COUNTA()
1. Speed of calculation. Relevant if the data is large. Just did a test with 20,000 rows of these functions COUNTA about 0.8 seconds, ROWS about 0.008 seconds.
2. ROWS would work on the data in column B in this thread even if there was no data in column A whereas COUNTA wouldn't (as far as I can see)
3. It's shorter to type. :)
 
Upvote 0
Thank you for the great response. I learned a new function today - aggregate!

I'll post more carefully in the future.

Cheers!
 
Upvote 0
Hi Ford
I can think of 3 reasons for using ROWS() instead of COUNTA()
1. Speed of calculation. Relevant if the data is large. Just did a test with 20,000 rows of these functions COUNTA about 0.8 seconds, ROWS about 0.008 seconds.
2. ROWS would work on the data in column B in this thread even if there was no data in column A whereas COUNTA wouldn't (as far as I can see)
3. It's shorter to type. :)

Peter, thanks for that. That was exactly the type of answer I was hoping for. Many times, different functions are used purely based on preference, but sometimes, there is a constructive reason to use 1 over another.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top