Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rolling Moving Average with Blank Cells (2003 SP2)

    I have a spreadsheet with every day of the year filling down column F, weekends and holidays included. In Column I, I list Total Cash, which is a sum of detail in columns K through O for that day's activities. Essentially, I am trying to calculate a 20-day moving average of Total Cash. The basic problem for me is that there will be no Total Cash numbers for weekends and holidays, so I want to eliminate those days from the calculation. Currently, if I use a basic sum formula, dividing by 20, but of course, I have to manually change the range, given that weekends will fall in, and the occassional holiday. I have seen moving averages done, using arrays, sumproduct, sumif, dynamic ranges, etc--but haven't seen this issue of blanks being resolved, and I can't seem to figure it out or if I am making it too complicated. Any help appreciated.
    Steve

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rolling Moving Average with Blank Cells (2003 SP2)

    If 20 isn't a magic number, A simple solution would be to just use the average of the past "4 weeks". It will essentially ignore the blanks for the weekends and if there are no holidays in the time period it will be a "20-day average". if there are holidays (and extra blanks) there may be less days, but the "4-week" moving average will still be accurate...

    Steve

  3. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rolling Moving Average with Blank Cells (2003 SP2)

    That might work, but there is some variation on cash receipts relative to the day of the week--looks like things build up in the mail over the weekend and that sort of thing.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rolling Moving Average with Blank Cells (2003 SP2)

    A four-week average would appear to take that in its stride.

  5. #5
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rolling Moving Average with Blank Cells (2003 SP2)

    Perhaps you are right. I guess I am really looking at how to stick a formula in a cell that averages the last 20 cells with numbers above it. I could do a sumif with a condition to add only those cells with values, or that are not blank, but could figure out how to get a count limitation added.

    Thanks,
    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rolling Moving Average with Blank Cells (2003 SP2)

    See the attached sample workbook. As you can see, the moving average simply ignores blank cells. No SUMIF or conditions needed.

  7. #7
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rolling Moving Average with Blank Cells (2003 SP2)

    Looks like i overthunk this one too much. Thanks for you patience - the light is finally on.
    Cheers!

Posting Permissions

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