S
smurray444
Dear all,
I have a large dataset of global climate data, sourced from copious
meteorological stations throughout the world. However, due to equipment
failure (etc etc), the dataset is not entirely complete. I have a macro
which will interpolate parameter values where there is a value either
side of a blank cell (ie. a linear interpolation based on the
surrounding values), yet this is unable to be performed on cells at the
start or end of a time series (ie. the value for the first or last cell
of a particular station).
Just to clarify, a simplfied example is as follows:
Station 1: A = GAP (=10)
Station 1: B = 15
Station 1: C = 20
Station 1: D = 25
Station 1: E = 30
Station 1: F = 35
Station 1: G = GAP (=40)
Station 2: A = GAP (=4)
Station 2: B = 5
Station 2: C = 6
Station 2: D = 7
Station 2: E = 8
Station 2: F = 9
Station 2: G = GAP (=10)
As shown above, for the inter-station gap between station 1 and 2, the
values cannot be linearly interpolated based simply on the values
surrounding the gap. I'm not a statistical/mathamatical expert, but I
would guess that some form of regression formula could be used to fill
the beginning and end values, based on the trend of the existing data
for each individual station?
Is there a way of designing (a macro in Visual Basic?) to automate
this
procedure in Excel 2002? - as doing it manually would be unfeasible due
to the nature of the dataset size. Incidently, data which requires
interpolation is in columns F to M (inclusive), and is represented as a
blank cell; station reference is in column A in my dataset. Just a
couple of other bits of information: extrapolation is required
vertically down the columns, and not between rows. The time-step is
consistant in all cases, and is on a yearly basis. If possible, would
it be feasible to include a line (in a macro, say) that states that the
operation should only be performed on blank cells at the beginning and
end of the station data series (and not for any blanks in between, so
as not to risk the filling of unintended gaps which should ideally be
tackled by the interpolation macro).
Thanks very much for your help and time - if you have any questions,
please don't be afraid to get back in touch.
Thanks again,
Steve M
I have a large dataset of global climate data, sourced from copious
meteorological stations throughout the world. However, due to equipment
failure (etc etc), the dataset is not entirely complete. I have a macro
which will interpolate parameter values where there is a value either
side of a blank cell (ie. a linear interpolation based on the
surrounding values), yet this is unable to be performed on cells at the
start or end of a time series (ie. the value for the first or last cell
of a particular station).
Just to clarify, a simplfied example is as follows:
Station 1: A = GAP (=10)
Station 1: B = 15
Station 1: C = 20
Station 1: D = 25
Station 1: E = 30
Station 1: F = 35
Station 1: G = GAP (=40)
Station 2: A = GAP (=4)
Station 2: B = 5
Station 2: C = 6
Station 2: D = 7
Station 2: E = 8
Station 2: F = 9
Station 2: G = GAP (=10)
As shown above, for the inter-station gap between station 1 and 2, the
values cannot be linearly interpolated based simply on the values
surrounding the gap. I'm not a statistical/mathamatical expert, but I
would guess that some form of regression formula could be used to fill
the beginning and end values, based on the trend of the existing data
for each individual station?
Is there a way of designing (a macro in Visual Basic?) to automate
this
procedure in Excel 2002? - as doing it manually would be unfeasible due
to the nature of the dataset size. Incidently, data which requires
interpolation is in columns F to M (inclusive), and is represented as a
blank cell; station reference is in column A in my dataset. Just a
couple of other bits of information: extrapolation is required
vertically down the columns, and not between rows. The time-step is
consistant in all cases, and is on a yearly basis. If possible, would
it be feasible to include a line (in a macro, say) that states that the
operation should only be performed on blank cells at the beginning and
end of the station data series (and not for any blanks in between, so
as not to risk the filling of unintended gaps which should ideally be
tackled by the interpolation macro).
Thanks very much for your help and time - if you have any questions,
please don't be afraid to get back in touch.
Thanks again,
Steve M