Hi Nigel
no need for VBA, can be done with formulas :-)
Though there're some missing spots in the OP's specification

--
Regards
Frank Kabel
Frankfurt, Germany

Nigel wrote:
This is very possible in Excel but it will require a VBA programme.
A
couple of observations from your rules.

1. The number of periods pre and post,as you say, are dependent on
the
number of missing values. What happens if there are insufficient pre
and
post data values to provide the average?

2. The answer to the first question might resolve the difficulty I am
having
with the fact that data comes every hour. When do you need to
calculate and
know there are missing values? Is this driven by some internal clock
or at
the end of the day or some other control? Since you would not know
if data
is missing or if there are sufficient real values to calculate the
missing entries every hour.

Perhaps if you can help shed some light on how you want to address
this conundrum, we can provide some code.

Cheers
Nigel

"Michael DiCostanzo" wrote in message
m...
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




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==---- http://www.newsfeed.com The #1 Newsgroup Service in the
World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized
Servers - Total Privacy via Encryption =---