## Access VBA Linear Interpolation

## Access VBA Linear Interpolation

(OP)

I am trying to use Access VBA code to interpolate (linearly) and return values to a table. I am still very new to both Access and VBA coding, so please be kind and clear any responses--I appreciate it!

Going off of this formula: y2 = [(x2-x1)(y3-y1)/(x3-x1)] +y1

My x2 column is in a table called "Calculation Sheet", while my x1, x3, y1, and y3 values are in a table called "Lookup Table" (x1,x3 in the same column in increments of 50, y1 and y3 in another column). I am trying to get the Access VBA program to find the y2 value from interpolation "Lookup Table" and return the value back to "Calculation Sheet" in another column for y2, which corresponds to x2 values.

I am at a loss for how to execute something like this and what the best way would be (sub vs function, SQL vs VBA code, etc). Any assistance appreciated, thank you!

Going off of this formula: y2 = [(x2-x1)(y3-y1)/(x3-x1)] +y1

My x2 column is in a table called "Calculation Sheet", while my x1, x3, y1, and y3 values are in a table called "Lookup Table" (x1,x3 in the same column in increments of 50, y1 and y3 in another column). I am trying to get the Access VBA program to find the y2 value from interpolation "Lookup Table" and return the value back to "Calculation Sheet" in another column for y2, which corresponds to x2 values.

I am at a loss for how to execute something like this and what the best way would be (sub vs function, SQL vs VBA code, etc). Any assistance appreciated, thank you!

## RE: Access VBA Linear Interpolation

So how does x

_{2}relate to your lookup table? I'd GUESS that x_{2}are values between x_{1}and x_{3}I'm an Excel guy much more than an Access guy. I'd be apt to query the table from Excel and do the interpolation in an Excel sheet

So you'd have a column like...

...where 1 and 50 are values from Lookup Table and 10-40 are values from Calculation Sheet.

Unfortunately, you have supplied no other data: No Lookup Table values nor Calculation Sheet values.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Access VBA Linear Interpolation

Yes, my x2 values are between x1 and x3.

Let me provide some sample data, sorry about that:

x values (lookup)-----y values (lookup)

1820100------20000

1820150------ 19250

1820200------ 18500

1820250------ 18250

1820300------ 18000

and say my sample x2 value is 1820122.

## RE: Access VBA Linear Interpolation

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Access VBA Linear Interpolation

2) I broke the formula down into factors...

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Access VBA Linear Interpolation

TTFN

I can do absolutely anything. I'm an expert!

FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers

## RE: Access VBA Linear Interpolation

I've finished school so this is not an assignment. I'm trying to learn VBA coding in an Access environment.

## RE: Access VBA Linear Interpolation

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}