|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 22nd, 2005, 03:31 AM
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
December 22nd, 2005, 04:10 AM
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
December 22nd, 2005, 02:59 PM
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
December 22nd, 2005, 03:47 PM
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..
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Interpolation | Bent Hansen | Worksheet Functions | 3 | November 4th, 2005 03:59 PM |
| linear interpolation function in excel | tskoglund | Worksheet Functions | 4 | September 10th, 2005 03:31 AM |
| interpolation with forecast in a particular interval (HELP) | uriel78 | Worksheet Functions | 4 | February 21st, 2005 06:53 AM |
| interpolation in a particular interval (HELP) | uriel78 | General Discussion | 1 | February 19th, 2005 05:16 PM |
| Query with interpolation of dates/rates? | Red | Running & Setting Up Queries | 12 | November 19th, 2004 04:03 PM |
Linear Mode
