Interpolation in excel with VBA

Hi

I have just started to work on a routine for excel developed with VBA.

One of the sub tasks within the routine is to interpolate between different sets of datapoints. For this task I am using the following calculations:

=FORECAST(NewX,OFFSET(KnownY,MATCH(NewX,KnownX,1)-1,0,2),OFFSET(KnownX,MATCH(NewX,KnownX,1)-1,0,2))

Somehow I have allot of difficulty to implement this into my routine and I hope therefore someone could help me to solve this problem. See the attached file which included the specific part of the routine.

Best regards
John

Thanks for your input and help
Yes I also discovered a couple of minor errors in the code beside the interpolation function.

Originally Posted by Andy Pope

The offset function is not available via the object model.

Instead use the OFFSET object of the range reference.

Please Login or Register  to view this content.

This will get you to the next line in your routine, which fails due to x_val being -10, which is an invalid value for the Cells objects.

EZplot for Excel has an interpolate function. There is a trial version at www.OfficeExpander.com
Regards

Hi,

Is there any way to find the opposite, i.e. finding the x-value given the y-value? I tried reversing the axis in the formula but somehow it did not work. I believe it appears to be more than just reversing the axis. Thanks for helping~

PT