# Interpolation values from a curve data

• I have the values for 0, 30, 60, 90, 120, 150, 180 degree and its results values in a table. a continues curves can be plot from those value.

If I would like the results values for (Let say 160 degree) as a input with my other macro. What is the best way to do?

It will be ideal if the values lay within the curves. but if that is too complex. a linear interpolation between results values wil be fine.

I belived someone have done this before. just not able to find it.

Many thanks

• Re: Interpolation values from a curve data

see if the FORECAST function helps you

as per the help file

FORECAST

Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

also check out GROWTH and TREND

• Re: Interpolation values from a curve data

This the the data I have

Degree Results
0 0.015
30 0.013
60 0.011
90 0.092
120 0.022
150 0.013
180 0.017

Tried the forecast function, but the results gives 0.2459 when I put degree = 0. which could nto be correct. Please help

• Re: Interpolation values from a curve data

are you sure

using the INTERCEPT function I am getting 0.02432

(the value of "a" in Y=a+bx and Y=a if x=0)

...the same value I am getting even if I solve for Y=a+bx manually or if I use FORECAST

if you want I will upload the workbook

just to check your Degree col contains X-values (independent function) and "Results" column contain Y-values (dependent function) is it not???

• Re: Interpolation values from a curve data

From the following data

_____A_______B
1__Degree__Results
2 ___0______0.015
3 ___30_____0.013
4 ___60_____0.011
5 ___90_____0.092
6___120_____0.022
7___150_____0.013
8___180_____0.017

Below is how I put in formula bar. FORECAST(x,known_y's,known_x's)
which is =FORECAST(0,B2:B8,A2:A8), and it gives 0.02459.

where I am expecting a value of 0.015.

Did I do somthings wrong with the formula?

Many thanks

• Re: Interpolation values from a curve data

the FORECAST equation works on Y=a+bx+e where e is the error distributed normally with mean zero and StdDev of 1...therefore historical values of Y will have error values since any forecast value of Y is affected by other data pairs

to clarify if for x=180 you were to assign some other value of Y then your regression equation will change...a and b params will get some other values

AFAIK regression equations hardly give exact predicted values for historical data pairs since they are best fit equations....

pangolin

• Re: Interpolation values from a curve data

emm.. then I need to look for solution outwhere then degree = 15 and can not gives a result between 0.015 and 0.013.

• Re: Interpolation values from a curve data

Quote from Yoman

emm.. then I need to look for solution outwhere then degree = 15 and can not gives a result between 0.015 and 0.013.

I'd use a slightly different approach...

What's the corresponding formula or result for each degree input?

[SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

• Re: Interpolation values from a curve data

The results is a input from other worksheet which can't fit a curve into it.

I found a solution from other post.

http://www.ozgrid.com/forum/sh…7&highlight=interpolation

think it is linear, but I belive it work. Just one problem, when I put degree = 0, it gives error. when user input 0 degree, I change it to 0.0000001. It seen working and does not effect my result.

[vba]Function Linearinter22d(inputs As Range, X As Double, Y As Double) As Double

Dim nx As Long, ny As Long
Dim lowerx As Long, lowery As Long, upperx As Long, uppery As Long, i As Long

nx = inputs.Rows.Count
ny = inputs.Columns.Count

If X < inputs(2, 1) Then
lowerx = 2
upperx = 2
ElseIf X > inputs(nx, 1) Then
lowerx = nx
upperx = nx
Else
For i = 2 To nx
If inputs(i, 1) >= X Then
upperx = i
lowerx = i - 1
Exit For
End If
Next
End If

If Y < inputs(1, 2) Then
lowery = 2
uppery = 2
ElseIf Y > inputs(1, ny) Then
lowery = ny
uppery = ny
Else
For i = 1 To ny
If inputs(1, i) >= Y Then
uppery = i
lowery = i - 1
Exit For
End If
Next
End If

Dim XL As Double, XU As Double, YL As Double, YU As Double
Dim temp1 As Double, temp2 As Double

XL = inputs(lowerx, 1)
XU = inputs(upperx, 1)
YL = inputs(1, lowery)
YU = inputs(1, uppery)
temp1 = (inputs(lowerx, lowery) * (XU - X) _
+ inputs(upperx, lowery) * (X - XL)) / (XU - XL)
temp2 = (inputs(lowerx, uppery) * (XU - X) _
+ inputs(upperx, uppery) * (X - XL)) / (XU - XL)
Linearinter22d = (temp1 * (YU - Y) + temp2 * (Y - YL)) / (YU - YL)

End Function

[/VBA]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!