+ Reply to Thread
Results 1 to 3 of 3

30 Day Moving Average Ignoring Blank Cells

  1. #1
    ethatch
    Guest

    30 Day Moving Average Ignoring Blank Cells

    I have a column of data covering 365 days, some of the cells have data and
    some don't. I am trying to figure out how to calculate a 30 day moving
    average ignoring blank cells. In other words, I want the average of the last
    30 days that have a data value in the cell. The moving average may have to
    look back at the last 50 to 60 days in order to get 30 days that have values
    to average. I would appreciate any suggestions how to calculate this.

  2. #2
    Biff
    Guest

    Re: 30 Day Moving Average Ignoring Blank Cells

    Hi!

    What if there aren't 30 days of data to average?

    Assume your values are in the range A1:A365

    Entered as an array using the key combo of CTRL,SHIFT,ENTER:

    =AVERAGE(A365:INDEX(A1:A365,LARGE(IF(A1:A365<>"",ROW(A1:A365)),30)))

    If there aren't 30 values this version will average all values until there
    are 30 or more, then it will average the last 30.

    =AVERAGE(A365:INDEX(A1:A365,LARGE(IF(A1:A365<>"",ROW(A1:A365)),MIN(COUNT(A1:A365),30))))

    Biff

    "ethatch" <[email protected]> wrote in message
    news:[email protected]...
    >I have a column of data covering 365 days, some of the cells have data and
    > some don't. I am trying to figure out how to calculate a 30 day moving
    > average ignoring blank cells. In other words, I want the average of the
    > last
    > 30 days that have a data value in the cell. The moving average may have
    > to
    > look back at the last 50 to 60 days in order to get 30 days that have
    > values
    > to average. I would appreciate any suggestions how to calculate this.




  3. #3
    Harlan Grove
    Guest

    Re: 30 Day Moving Average Ignoring Blank Cells

    "ethatch" <[email protected]> wrote...
    >I have a column of data covering 365 days, some of the cells have data and
    >some don't. I am trying to figure out how to calculate a 30 day moving
    >average ignoring blank cells. In other words, I want the average of the
    >last
    >30 days that have a data value in the cell. The moving average may have to
    >look back at the last 50 to 60 days in order to get 30 days that have
    >values
    >to average. I would appreciate any suggestions how to calculate this.


    If your data were in C5:C369, then you could calculate 30 day moving
    averages using array formulas like

    D5:
    =IF(AND(COUNT(C$5:C5)>=30,COUNT(C5)),
    AVERAGE(INDEX(C$5:C5,MATCH(COUNT(C$5:C5)-29,
    MMULT(--(ROW(C$5:C5)>=TRANSPOSE(ROW(C$5:C5))),--ISNUMBER(C$5:C5)),
    0)):C5),"")

    Fill D5 down into D6:D369. This will evaluate to "" until you reach the row
    with the 30th number in col C and on any row thereafter in which col C
    doesn't contain a number.



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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