Interpolation Visual Basic Macro!!!!

[L. Tucker]

Does anyone have an interpolation visual basic macro? For
example, I have two columns of data (let's call them x and
y). Now I have a cell with a x-value somewhere in the
range of the x data and want an interpolated "y" value
output. Please help! Thanks so much.

 

[Dan E]

Function Interpolation(XVal As Double, XRange As Range, YRange As Range)
If XRange.Cells(1) < XRange.Cells(2) Then
For i = 1 To XRange.Cells.Count
If XRange.Cells(i) > XVal And i > 1 And XRange.Cells(i - 1) < XVal Then
X1 = XRange.Cells(i - 1).Value
X2 = XRange.Cells(i).Value
Y1 = YRange.Cells(i - 1).Value
Y2 = YRange.Cells(i).Value
End If
Next
Else
For i = 1 To XRange.Cells.Count
If XRange.Cells(i) < XVal And i > 1 And XRange.Cells(i - 1) > XVal Then
X2 = XRange.Cells(i - 1).Value
X1 = XRange.Cells(i).Value
Y2 = YRange.Cells(i - 1).Value
Y1 = YRange.Cells(i).Value
End If
Next
End If
If X1 = "" Then
Interpolation = "Cannot Extrapolate"
Exit Function
End If
Interpolation = (X1 - XVal) / (X2 - X1) * (Y2 - Y1) + Y1
End Function

Dan E

 

[Dan E]

MY MISTAKE,

That last formula should be:

Interpolation = -(X1 - XVal) / (X2 - X1) * (Y2 - Y1) + Y1

Dan E

 

[Dan E]

Harlan,

Here's a modified version if you really have to have your table
start in row 1

Function Interpolation(XVal As Double, XRange As Range, YRange As Range)
If XRange.Cells(1) < XRange.Cells(2) Then
For i = 1 To XRange.Cells.Count
If XRange.Cells(i + 1) >= XVal And XRange.Cells(i) <= XVal Then
X1 = XRange.Cells(i).Value
X2 = XRange.Cells(i + 1).Value
Y1 = YRange.Cells(i).Value
Y2 = YRange.Cells(i + 1).Value
End If
Next
Else
For i = 1 To XRange.Cells.Count
If XRange.Cells(i + 1) <= XVal And XRange.Cells(i) >= XVal Then
X2 = XRange.Cells(i).Value
X1 = XRange.Cells(i + 1).Value
Y2 = YRange.Cells(i).Value
Y1 = YRange.Cells(i + 1).Value
End If
Next
End If
If X1 = "" Then
Interpolation = "Cannot Extrapolate"
Exit Function
End If
Interpolation = -(X1 - XVal) / (X2 - X1) * (Y2 - Y1) + Y1
End Function

A UDF is nicer for interpolation because putting

=INTERPOLATION(C1, A1:A100, B1:B100) is much simpler
and easier to remember than:

=TREND(
OFFSET(YRange,MATCH(XVal,XRange,SIGN(INDEX(XRange,2)-N(XRange)))-1,0,2,1),
OFFSET(XRange,MATCH(XVal,XRange,SIGN(INDEX(XRange,2)-N(XRange)))-1,0,2,1),
XVal)

Dan E

That last formula should be:

Interpolation = -(X1 - XVal) / (X2 - X1) * (Y2 - Y1) + Y1 ..

Better to define all local variables - easier to catch bugs, faster execution if
variables used as counters are of explicit integer types rather than variants
holding integers.
..

Bug. VB[A] doesn't perform 'short-circuit' boolean valuations like C, C++ and
Java do. It obstinately evaluates all expressions in the conditional of an If
statement. So on the first iteration of the For loop when i equals 1, VBA most
definitely does evaluate XRange.Cells(i - 1), which throws a runtime error. You
need to use nested Ifs to do this in VB[A].

VBA is unnecessary for this. Worksheet functions could be used, e.g.,

=TREND(
OFFSET(YRange,MATCH(XVal,XRange,SIGN(INDEX(XRange,2)-N(XRange)))-1,0,2,1),
OFFSET(XRange,MATCH(XVal,XRange,SIGN(INDEX(XRange,2)-N(XRange)))-1,0,2,1),
XVal)

if XRange and YRange were both single column, multiple row ranges.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

 

[Harlan Grove]

Here's a modified version if you really have to have your table
start in row 1

Function Interpolation(XVal As Double, XRange As Range, YRange As Range)
If XRange.Cells(1) < XRange.Cells(2) Then
For i = 1 To XRange.Cells.Count
If XRange.Cells(i + 1) >= XVal And XRange.Cells(i) <= XVal Then

...

And now when i = XRange.Cells.Count the call to XRange.Cells(i + 1) will throw a
runtime error. Not a fix. Do you try testing your VBA code before posting?

One solution would be

For i = 2 To XRange.Cells.Count
If XRange.Cells(i - 1).Value < XVal And XVal <= XRange.Cells(i).Value Then

A UDF is nicer for interpolation because putting

=INTERPOLATION(C1, A1:A100, B1:B100) is much simpler
and easier to remember than:

Granted, but that argument carried to its absurd extreme would suggest using a
udf for anything that requires more than one built-in function call. UDFs are
slower than built-in functions by multiples of built-in function execution time.
It's possible 11 built-in function calls would be slower than one udf, but it's
not guaranteed to be so.

But if a udf is truly needed, there are ways to do this more compactly.


Function interp(v As Double, x As Range, y As Range) As Variant
Dim n As Long

On Error Resume Next
n = Application.Match(v, x, Sgn(x.Cells(2).Value - x.Cells(1).Value))

If Err.Number <> 0 Then
Err.Clear
interp = CVErr(xlErrNA)
Exit Function

ElseIf n = x.Cells.Count Then
interp = IIf(v = x.Cells(n).Value, y.Cells(n).Value, CVErr(xlErrNum))
Exit Function

End If

interp = (v - x.Cells(n).Value) / (x.Cells(n + 1).Value - x.Cells(n).Value)
interp = y.Cells(n).Value * (1 - interp) + y.Cells(n + 1).Value * interp
End Function

 

[L. Tucker]

Wow! Thanks to both of you for your quick replies!
Unfortunately, I need you to "dumb-it-down" for me. How do
I execute these macros (or use the function). I keep
getting a #NAME! error. Could you be more specific on how
to execute (or use) the macro and/or function?
Thanks again. You're both saving me tons of time!!!
Leslie

-----Original Message-----
... (i) <= XVal Then
...

And now when i = XRange.Cells.Count the call to

XRange.Cells(i + 1) will throw a

 

[Dan E]

To use the macro:

Press Alt + F11 (Opens the Excel VBA window)
You will see VBAProject(Your book name)
Right Click and choose insert module.
A Code window should appear in the area on the left
(If not double click Module1 (Under the explorer))
Paste the VBA Code (Function) into the window
Close the VBA window

Now you can use the function just like the excel functions
It'll be listed in the function wizard under "User Defined"
called interp or interpolation (depending on who's code)

If you had your X Range A2:A15 and Y Range B2:B15
and your X Value in C1

=Interpolation(C1, A2:A15, B2:B15) (with my function)
=interp(C1, A2:A15, B2:B15) (with harlans)

The Worksheet version
=TREND(OFFSET(B2:B15,MATCH(C1,XRange,SIGN
(INDEX(A2:A15,2)-N(A2:A15)))-1,0,2,1),OFFSET(
A2:A15,MATCH(C1,XRange,SIGN(INDEX(A2:A15,2)
-N(A2:A15)))-1,0,2,1),C1)

Dan E