# Thread: Rolling Moving Average with Blank Cells (2003 SP2)

1. ## 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. ## 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. ## 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. ## Re: Rolling Moving Average with Blank Cells (2003 SP2)

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

5. ## 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. ## 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. ## 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
•