Nice one Frank! - but the missing data elements would cause me a problem.
You still end up with missing data and the objective was to resolve this. I
think we need to get the OP to clarify how to deal with this.
Cheers
Nigel
"Frank Kabel" wrote in message
...
Hi Michael
try the following approach (using helper columns to make the formulas a
little bit less complex)
- Add a column B as helper column and enter the following array formula
in B2 (this is your second data row as row 1 always should contain
data). Formula ist entered with CTRL+SHFT+ENTER
=IF(A2="",MIN(IF(A3:$A$20<"",ROW(A3:$A$20),20))," ")
copy down for all rows
- add the helper column C and enter the following formula in C2 (also
array entered)
=IF(A2="",MAX(IF(A$1:A1<"",ROW($A$1:A1),0)),"")
and copy down
- if you like you can hide columns B+C
- now enter the following formula in D2 (this is the column with your
corrected data)
=IF(A2="",SUM(INDIRECT("A" & C2 & ":A" & C2-(B2-C2-2)),INDIRECT("A" &
B2 & ":A" & B2 + (B2-C2 - 2)))/(B2-C2-1),"")
and copy down
Notes:
this approach won't work in all circumstances. e.g.
- the first row is empty (as there is no data before
- the last row ist empty
- There are not enough rows before or after
--
Regards
Frank Kabel
Frankfurt, Germany
Michael DiCostanzo wrote:
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 =---