Thanks:  0

# Thread: Moving average in table with non-numeric data

1. ## Moving average in table with non-numeric data

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).

Unknown
A B C
1 Date2 ARI ARI2
2 42099 #DIV/0!
3 42100 6.91
4 42101 14.86
5 42102 4.24
6 42103 #DIV/0!
7 42104 7.45
8 42105 10.65
9 42106 6.24
10 42107 14.86
11 42108 3.84
12 42109 7.68
13 42110 15.99 9.27
14 42111 17.14 9.99
15 42112 5.34 9.84
16 42113 10.19 9.42
17 42114 #DIV/0! 9.94
18 42115 3.84 9.38
19 42116 12.95 9.88
20 42117 #DIV/0! 9.81
Sheet2

Worksheet Formulas
Cell Formula
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)

Array Formulas
Cell Formula
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))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

2. ## Re: Moving average in table with non-numeric data

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

3. ## Re: Moving average in table with non-numeric data

Originally Posted by Reboshua
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.

Moving average

 A B C 1 Date2 ARI ARI2 2 42099 #DIV/0! 3 42100 6.91 4 42101 14.86 5 42102 4.24 6 42103 #DIV/0! 7 42104 7.45 8 42105 10.65 9 42106 6.24 10 42107 14.86 11 42108 3.84 12 42109 7.68 13 42110 15.99 9.27 14 42111 17.14 9.99 15 42112 5.34 9.84 16 42113 10.19 9.42 17 42114 #DIV/0! 9.94 18 42115 3.84 9.38 19 42116 12.95 9.88 20 42117 #DIV/0! 9.81 21

 Cell Formula C2 =IF(ROWS(C\$2:C2)<12,"",AGGREGATE(1,6,INDEX(B\$2:B2,ROWS(C\$2:C2)-11):B2))

Excel tables to the web >> Excel Jeanie HTML 4

4. ## Re: Moving average in table with non-numeric data

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() )

5. ## Re: Moving average in table with non-numeric data

Originally Posted by FDibbins
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.

6. ## Re: Moving average in table with non-numeric data

Thank you for the great response. I learned a new function today - aggregate!

I'll post more carefully in the future.

Cheers!

7. ## Re: Moving average in table with non-numeric data

Originally Posted by Reboshua
Thank you for the great response. I learned a new function today - aggregate!

I'll post more carefully in the future.

Cheers!
You are very welcome.

8. ## Re: Moving average in table with non-numeric data

Originally Posted by Peter_SSs
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.