Re: Automatically substituting missing data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nigel (nigel-9sw_at_nxospxamx.supanet.com)
Date: 03/06/04 

Date: Sat, 6 Mar 2004 08:22:57 -0000

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" <frank.kabel@freenet.de> wrote in message
news:uNsLh70AEHA.744@TK2MSFTNGP10.phx.gbl...
> 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" <frank.kabel@freenet.de> wrote in message
> > news:u4W2kn0AEHA.684@tk2msftngp13.phx.gbl...
> >> 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 =---

Relevant Pages