Automating Extrapolation

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

Has anyone had any luck with this?