Conditionally extending range of rolling average -- ??

[burro]

Hi folks,

I work with sets of data in which there is data provided for each hour over periods of several years.

I often have to calculate "rolling averages" in which the result displays the average of values over the prior 30 days (the prior 720 hourly entries). That part is easy enough -- on the 721st record (on row 722, since I have a header row), and those further down, I sum the entries of the previous 720 rows from the desired column.

Here's the hitch:

I need to find a way to extend the range under certain conditions.

Specifically, whenever there are no entries during a whole calendar day ((all 24 hourly entries for that particular date are blank), then I need to skip that date and not count it as one of the days in the "30-day" average, and extend the range of rows averaged by an extra day (an extra 24 rows).

This can happen several times in a month. For example, if there are three days with no data entries at all, then I really need to average over the previous 33 days (792 hours / 792 rows), to make up for the 3 days with no data. If there are 7 days with no data, then I really need to average existing entries over 37 days, and so forth.

Is there a good way to set up a calculation to do this?

Desperately needed. Any help much appreciated.

Thanks.

[arthurbr]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[burro]

Thanks.

Here is a super-pared-down version of a sample data set:
http://www.sendspace.com/file/d5pvz0 (~18kb)

In this file, I have assumed that a day has only three hours in it (not 24)
and I've set up a 5-day rolling average (not 30-day).

On the single worksheet, I've included:
1.

1. An example of how I am calculating rolling averages now
2. And two ways cells could be selected to get the new desired result ("Method B" would be the most useful for our purposes):
   A. Extending the 'look-back' range by a number of cells equal to the number of cells in the range that occur on entirely blank days
   B. Entirely omitting from the look-back calculation any cells that occur on entirely blank days

In order to set up this example, I had to manually enter the rolling average calculations in most of the cells for each of the "After" cases. Obviously, that is not practicable when I am dealing with tens or hundreds of thousands of rows.

So, the goal is that the "rolling average" column should have a single, uniform calculation.

If it is not possible to do this with a calculation, is there some other way?

Thank you.

[burro]

If that problem is too hard, there is a much simpler challenge that would help:

Forgetting about hours, for this moment, just consider daily data.

I need to be able to average the previous 5 non-blank data entries.

So if there is one or more blanks in the previous rows the cell range selected for the average function has to be extended. (See attached file.)

Is there a way to do this?

Is there a way using dynamic range functions?

Anything, anything that could help would be most appreciated!

[martindwilson]

ok with a helper column
see cols j:k