Hi Nigel
ah, now I understand and yes you're corect (e.g. if the first entry is
missing or the second and third)

--
Regards
Frank Kabel
Frankfurt, Germany

Nigel wrote:
Hi Frank,
Is it not the case that if there insufficient entries pre and post
the
missing entries that you cannot (actually should not) compute the
intermediate values, hence you still having missing data. That was
my
reference to 'Still end with missing data elements'

If data remains missing or is incalculable by some form of rule
induction
then this should be declared as such, alternatively new rules for
this
induction need to be prescribed. The OP needs to provide these rules
based
on his analytical process.

Cheers
Nigel


"Frank Kabel" wrote in message
...
Hi Nigel
I agree that the OP has to clarify his specification :-)
But what do you mean with 'Still end with missing data elements'. At
least for his example this works (though I'm also wondering how such
source data could be created...)

Greetings
Frank


--
Regards
Frank Kabel
Frankfurt, Germany

Nigel wrote:
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 =---





----== 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 =---