| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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.. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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.. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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.. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I am looking for a function for linear interpolation | Excel Discussion (Misc queries) | |||
| interpolation in a particular interval (HELP) | Excel Discussion (Misc queries) | |||
| help with interpolation and limit of interpolation | Excel Discussion (Misc queries) | |||
| interpolation in excel | Excel Worksheet Functions | |||
| interpolation XY data with bezier curver | Charts and Charting in Excel | |||
Hybrid Mode
