Interpolation

[karaeloko]

Ok. I created an excel spreadsheet that interpolates data from two different
dates in order to calculate the results for a date that has no data. The
problem is that it is a very slow process to find info for two different
dates, paste it on my template to then get the calculations and paste that
data in another sheet.

Basically what I need is some VBA function that would grab amounts for two
different dates and calculate what the amount would be for a date that is in
between those two automatically.

Let´s say I have A1=03/May/2006, and my data doesn´t have data for that day,
but it has info for 02/May/2006 and 06/May/2006. I need the VBA to detect
those two dates (which always vary by the way) on column A and interpolate
the amounts on Column B (don't worry about an interpolation function).

Is it possible?

 

[merjet]

Here is a user-defined function that will do it. 'dt3' is the date for
which you want a value that falls between val1 and val2.

Function Interpolate(dt3 As Date, dt1 As Date, dt2 As Date, _
val1 As Double, val2 As Double) As Double
Interp = (dt2 - dt3) / (dt2 - dt1) * val1 + (dt3 - dt1) / (dt2 -
dt1) * val2
End Function

Hth,
Merjet