I'm doing a project for a client where I have inherited someone else's
data which is kept in Excel files. Here's what I want to do.

The data is updated each hour. Sometimes we get no data or bad data
for short periods of time. A sample of the data is shown below.

In this example, there is no data for hours 5-6. Since we have two
hours worth of missing data, we are required to substitute for the
missing data by taking the average of the data two hours before and
two hours after the period when the data is missing. (These are the
rules we have to live by.)

In this case, the numbers would be calculated as follows:

(7.60 + 7.55 + 7.45 + 7.50) / 4 = 7.525

The value of 7.525 is then substituted into the empty cells for hours
5-6 as shown in the Corrected_Data column.


Hour Raw_Data Corrected_Data
1 7.51 7.51
2 7.45 7.45
3 7.60 7.60
4 7.55 7.55
5 7.525
6 7.525
7 7.45 7.45
8 7.50 7.50

If there were 3 hours of missing data, then we would have substituted
for the missing data by taking the average of the data three hours
before and three hours after the period when the data is missing. I
currently do the substitution manually. I really want to append the
raw data into a spreadsheet and then calculate the Corrected_Data
automatically.

Can anyone tell me how to do that in Excel? As I mentioned, I've
inherited this system, so I have to live with it for now. We're
looking to replace it and if the answer is that this can't be done in
Excel, that's OK. I want to transition to a newer system as smoothly
as possible.

Thanks in advance