Interpolation

[teen]

how can i interpolate if the given values are:

WIND ANGLE Q(DEG) CA CS CM


0 0.00397 0.00000 0.000000
10 0.00394 -0.00012 -0.000065
20 0.00369 -0.00013 -0.000097
30 0.00398 -0.00008 -0.000108
40 0.00408 0.00002 -0.000137
50 0.00426 0.00023 -0.000177
60 0.00422 0.00062 -0.000223
70 0.00350 0.00117 -0.000020
80 0.00195 0.00097 0.000256
90 -0.00003 0.00088 0.000336
100 -0.00103 0.00098 0.000338
110 -0.00118 0.00106 0.000343
120 -0.00117 0.00117 0.000366
130 -0.00120 0.00120 0.000374
140 -0.00147 0.00114 0.000338
150 -0.00198 0.00100 0.000278
160 -0.00222 0.00075 0.000214
170 -0.00242 0.00037 0.000130
180 -0.00270 0.00000 0.000000
190 -0.00242 -0.00037 -0.000130
200 -0.00222 -0.00075 -0.000214
210 -0.00198 -0.00100 -0.000278
220 -0.00147 -0.00114 -0.000338
230 -0.00120 -0.00120 -0.000374
240 -0.00117 -0.00117 -0.000366
250 -0.00118 -0.00106 -0.000343
260 -0.00103 -0.00098 -0.000338
270 -0.00003 -0.00088 -0.000336
280 0.00195 -0.00097 -0.000256
290 0.00350 -0.00117 0.000020
300 0.00422 -0.00062 0.000223
310 0.00426 -0.00023 0.000177
320 0.00408 -0.00002 0.000137
330 0.00398 0.00008 0.000108
340 0.00396 0.00013 0.000097
350 0.00394 0.00012 0.000065

Angle of Wind CA CS CM

121.66 -0.001175 0.001175 0.000367


IF I TYPE 121.66 I WANT THE EXCELL TO LOOK UP THE VALUE FROM THE EQUIVALENT
VALUE OF CA CS CM..

THANK YOU.. HOPE YOU COULD HELP ME..

 

[duane]

i put your table in cells a3:d38 and your desired wind angle in cell a41

in cell a43
=OFFSET($A$2,MATCH($A$41,$A$3:$A$38,1),0)

and in cell a44
=OFFSET($A$2,MATCH($A$41,$A$3:$A$38,1)+1,0)

this gives the wind angles below and above you desired wind angle

in cells b43 and copied thru d44
=VLOOKUP($A43,$A$3:$D$38,COLUMN())

in cell b41 copied thru d41

=($A$41-$A$43)/($A$44-$A$43)*(B44-B43)+B43

 

[Niek Otten]

You could also use this User Defined Function
You use it much the way you would use VLOOKUP(), but it interpolates
If you don't know how to insert a UDF, look here first:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

' =========================================================================
Function TabInterpol(ToFind As Double, Table As Range, ColumnNo As Long)
' Niek Otten
' Works like Vlookup, but interpolates
' Numbers only!

Dim RowNrLow As Long
Dim RowNrHigh As Long
Dim TableEntryLow As Double
Dim TableEntryHigh As Double
Dim ToFindLow As Double
Dim ToFindHigh As Double
Dim i As Long
Dim a As Double
Dim VBATable

VBATable = Table ' read table into VBA for speed

If ToFind < VBATable(1, 1) Or ToFind > VBATable(UBound(VBATable, 1), 1) Then
TabInterpol = CVErr(xlErrNA)
Exit Function
End If

If ToFind = VBATable(1, 1) Then ' do not interpolate for bottom
TabInterpol = VBATable(1, ColumnNo)
Exit Function
End If

If ToFind = VBATable(UBound(VBATable), 1) Then ' do not interpolate for end
of table
TabInterpol = VBATable(UBound(VBATable, 1), ColumnNo)
Exit Function
End If

For i = 1 To Table.Rows.Count
a = VBATable(i, 1)
If a > ToFind Then
RowNrLow = i - 1
Exit For
End If
Next i

If ToFind = a Then
TabInterpol = VBATable(RowNrLow, ColumnNo)
Exit Function
End If

RowNrHigh = RowNrLow + 1
TableEntryLow = VBATable(RowNrLow, ColumnNo)
TableEntryHigh = VBATable(RowNrHigh, ColumnNo)
ToFindLow = VBATable(RowNrLow, 1)
ToFindHigh = VBATable(RowNrHigh, 1)
TabInterpol = TableEntryLow + (ToFind - ToFindLow) / (ToFindHigh -
ToFindLow) _
* (TableEntryHigh - TableEntryLow)

End Function
' =========================================================================

 

[Niek Otten]

Be aware of some unintended line wraps