Interpolation

teen · 12-21-2005, 11:35 PM

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 · 12-22-2005, 12:15 AM

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

"teen" wrote:

> 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..
>

Niek Otten · 12-22-2005, 11:10 AM

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
' =========================================================================

--
Kind regards,

Niek Otten

"duane" <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>
>
> "teen" wrote:
>
>> 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..
>>

Niek Otten · 12-22-2005, 11:50 AM

Be aware of some unintended line wraps

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> 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
> '
> =========================================================================
>
>
> --
> Kind regards,
>
> Niek Otten
>
> "duane" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>>
>>
>>
>> "teen" wrote:
>>
>>> 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..
>>>
>
>