Moving average in table with non-numeric data

Thanks Thanks:  0
Results 1 to 8 of 8

Thread: Moving average in table with non-numeric data

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    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. #2
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,669
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,598
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Moving average in table with non-numeric data

    Quote Originally Posted by Reboshua View Post
    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

    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

    Spreadsheet Formulas
    CellFormula
    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
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  4. #4
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,669
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,598
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Moving average in table with non-numeric data

    Quote Originally Posted by FDibbins View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,598
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Moving average in table with non-numeric data

    Quote Originally Posted by Reboshua View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  8. #8
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,669
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Moving average in table with non-numeric data

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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com