ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   Interpolation (https://www.excelbanter.com/new-users-excel/61546-interpolation.html)

teen
Interpolation
 
how can i interpolate if the given values a

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
Interpolation
 
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 a

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
Interpolation
 
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" wrote in message
...
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 a

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
Interpolation
 
Be aware of some unintended line wraps

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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" wrote in message
...
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 a

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


All times are GMT +1. The time now is 10:33 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com