+ Reply to Thread
Results 1 to 11 of 11

variable moving average periods

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    variable moving average periods

    Need to calculate moving average of column B, but I want to experiment by inputting different periods. I've attached a worksheet where the input cell is D4.

    The MA calculations can go into column D. Obviously the first MA data point will depend on the period in D4.

    Example calculation: Moving Average is 3. Therefore the first cell with data (2) will be D7.

    I very much appreciate your help!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-21-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Office 2016
    Posts
    146

    Re: variable moving average periods

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: variable moving average periods

    Thank you!

  4. #4
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: variable moving average periods

    Hi, I marked this "solved" before trying it out. The formula is not returning what I expected. Cell b4 is a label, not a number. And cell D4 in the formula is relative, whereas I'd think that it would be absolute, since it is the "input" cell.

    Can you take a look? Thanks.

  5. #5
    Forum Contributor
    Join Date
    11-21-2015
    Location
    Philippines
    MS-Off Ver
    Microsoft Office 2016
    Posts
    146

    Re: variable moving average periods

    I think when you use the Offset function, you can apply flexibility in your posts.
    Please Login or Register  to view this content.
    You can learn more about Offset function?

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: variable moving average periods

    Maybe like this ...

    =AVERAGE(INDIRECT("B"&4+D4&":B1048576"))

  7. #7
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: variable moving average periods

    I think that's getting closer. Lets take cell D11 for example. If the input (cell D4) is 3, then D11 should calculate the average of cells B9:B11.

    If D4 is 4, then D11 should calculate the average of B8:B11. (which is the average of the last 3 periods).

    So, returning to the question in my original post, should the formula for D7 be something like: =AVERAGE(INDIRECT(B7:whatever formula is B 7 minus 3)?

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: variable moving average periods

    Quote Originally Posted by jrtaylor View Post
    like: =AVERAGE(INDIRECT(B7:whatever formula is B 7 minus 3)?
    Here ...

    =AVERAGE(INDIRECT("B7:B"&7-D4))

  9. #9
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: variable moving average periods

    Thanks but that formula only works for D7. If I copy down, the formula returns the same answer (2).

  10. #10
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: variable moving average periods

    Try ...

    =AVERAGE(INDIRECT("B"&ROW()&":B"&ROW()-$D$4))
    Last edited by Phuocam; 10-01-2016 at 09:58 AM.

  11. #11
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: variable moving average periods

    Thanks! If I change it to: =AVERAGE(INDIRECT("B"&ROW()&":B"&ROW()-($D$4-1))) it works.
    Last edited by jrtaylor; 10-01-2016 at 01:03 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-20-2014, 03:20 PM
  2. [SOLVED] Calculate Average Price For Various Periods
    By Flanders in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-14-2014, 02:24 PM
  3. Moving average across variable range
    By SRKfc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-03-2013, 06:11 PM
  4. [SOLVED] How to calculate a rolling average over X periods
    By Johnex777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2013, 10:37 AM
  5. Charts 5 day moving average, 10 day moving average
    By monalisa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2008, 09:50 PM
  6. Moving average of a moving average in the same cell
    By philroberts1983 in forum Excel General
    Replies: 8
    Last Post: 09-16-2008, 07:36 AM
  7. average rolling periods
    By RobPatrick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2007, 05:26 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1