Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
I will try to explain this the best I can.
I have to do a calcualtion. The answer to that calculation will then be used to look up data in a table. For Example A B C D 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 My calculated answer was 6.67. How do I write a formula so that excel will return the corresponding values for A B C & D for 6.67. I have tried but the VLOOKUP function but it keeps returning the lower values which is not what I want. Yours hopefully Simon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
Hi,
You told us what you don't want, but what do you want? Also, VLOOKUP would not work with the sample you showed us because the 6.70 and 6.60 are not in your table. If you put those values as the first column of the lookup table then IF what you want is to have all vaules over 6.60 use the resutls of the 6.70 row then change your formula to =VLOOKUP(ROUNDUP(H1,1),A1:E10,2,False) In this case the value 6.67 is in H1 and the lookup table is in A1:E10 with A1:A10 containing the lookup values A B C D E 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 If this helps, please clickt the Yes button, Cheers, Shane Devenshire "hellmouthpirate" wrote: I will try to explain this the best I can. I have to do a calcualtion. The answer to that calculation will then be used to look up data in a table. For Example A B C D 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 My calculated answer was 6.67. How do I write a formula so that excel will return the corresponding values for A B C & D for 6.67. I have tried but the VLOOKUP function but it keeps returning the lower values which is not what I want. Yours hopefully Simon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
Try this starting in A1.
6.6 13657 178.3 70.2 67.79 6.7 13886 179.9 70.36 67.68 6.67 13817.3 179.42 70.312 67.713 A5 contains your calculated value. B5 contains the following formula which is copied to C5, D5 and E5. =(1-(($A$2-$A5)/($A$2-$A$1)))*(B$2-B$1)+B$1 I did change the value in D2 because it is lower than D1. If your data is really variable like that, you could modify the formula to accommodate it. HTH "hellmouthpirate" wrote: I will try to explain this the best I can. I have to do a calcualtion. The answer to that calculation will then be used to look up data in a table. For Example A B C D 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 My calculated answer was 6.67. How do I write a formula so that excel will return the corresponding values for A B C & D for 6.67. I have tried but the VLOOKUP function but it keeps returning the lower values which is not what I want. Yours hopefully Simon |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
your explanation is vague
what are you trying to accomplish? On 27 Lut, 14:42, hellmouthpirate wrote: I will try to explain this the best I can. I have to do a calcualtion. The answer to that calculation will then be used to look up data in a table. For Example * * * * * * * * * * A * * * * * * B * * * * * * C * * * * * *D 6.60 * * * * * 13657 * * * 178.3 * * *70.20 * * *67.79 6.70 * * * * * 13886 * * * 179.9 * * *70.16 * * *67.68 My calculated answer was 6.67. How do I write a formula so that excel will return the corresponding values for A B C & D for 6.67. I have tried but the VLOOKUP function but it keeps returning the lower values which is not what I want. Yours hopefully Simon |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
Hello Simon,
Values in A1:E4: A B C D 6.6 13657 178.3 70.2 67.79 6.7 13886 179.9 70.16 67.68 6.67 13817.3 179.42 70.172 67.713 [Please note that title A with numbers 13657, 13886, ... resides in columnB!] Formula in B4: =B2+(B3-B2)*($A4-$A2)/($A3-$A2) Copy across C4:E4. Regards, Bernd |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
Maybe my explanation was not the best. I have a data table that i have
incorporated into Excel. An example is: A B C D E 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 when the answer in a cell is 6.60 or 6.70 i have used the LOOKUP function and Excel returns all the values asspciated with that answer. I need those values to continue with the calculation. The problem I have now is what happens when my answer lies between 6.60 and 6.70, I need the interpolated values returned now. For example, for 6.67 the releavnt answer from column B should be 13817.3. I would like excel to do this automatically. I cannot write an interpolation formula as suggested previously as there are 80 rows and 12 columns of data so it would take me forever. I have tried the TREND function but the answer it returns is incorrect. It returns an answer in excess of 13886 which cannot possibly be right. "hellmouthpirate" wrote: I will try to explain this the best I can. I have to do a calcualtion. The answer to that calculation will then be used to look up data in a table. For Example A B C D 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 My calculated answer was 6.67. How do I write a formula so that excel will return the corresponding values for A B C & D for 6.67. I have tried but the VLOOKUP function but it keeps returning the lower values which is not what I want. Yours hopefully Simon |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
Hi,
Assume your data above is arranged in range B5:F6. Enter 6.67 in cell B9 and use the following formula in cell C9 =TREND(C5:C6,B5:B6,B9). The answer is exactly as mentioned by you - 13817.3 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "hellmouthpirate" wrote in message ... Maybe my explanation was not the best. I have a data table that i have incorporated into Excel. An example is: A B C D E 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 when the answer in a cell is 6.60 or 6.70 i have used the LOOKUP function and Excel returns all the values asspciated with that answer. I need those values to continue with the calculation. The problem I have now is what happens when my answer lies between 6.60 and 6.70, I need the interpolated values returned now. For example, for 6.67 the releavnt answer from column B should be 13817.3. I would like excel to do this automatically. I cannot write an interpolation formula as suggested previously as there are 80 rows and 12 columns of data so it would take me forever. I have tried the TREND function but the answer it returns is incorrect. It returns an answer in excess of 13886 which cannot possibly be right. "hellmouthpirate" wrote: I will try to explain this the best I can. I have to do a calcualtion. The answer to that calculation will then be used to look up data in a table. For Example A B C D 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 My calculated answer was 6.67. How do I write a formula so that excel will return the corresponding values for A B C & D for 6.67. I have tried but the VLOOKUP function but it keeps returning the lower values which is not what I want. Yours hopefully Simon |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
Thank you Ashish
The problem is my range of values. When I used the data directly above and below my required value I did indeed get the same answer as you. The formula though, used the first and last values in my table of data hence the discrepancy as the relationship is not entirely linear through all the values. I will keeping playing with it and see if I can solve it someo ther way. Ideally what I want is for excel to look up the values from the table and automatically select the values directly above and below my required answer and interpolate as needed. As i mentioned before I have 85 rows of data and 12 columns. Thank you for your efforts. "Ashish Mathur" wrote: Hi, Assume your data above is arranged in range B5:F6. Enter 6.67 in cell B9 and use the following formula in cell C9 =TREND(C5:C6,B5:B6,B9). The answer is exactly as mentioned by you - 13817.3 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "hellmouthpirate" wrote in message ... Maybe my explanation was not the best. I have a data table that i have incorporated into Excel. An example is: A B C D E 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 when the answer in a cell is 6.60 or 6.70 i have used the LOOKUP function and Excel returns all the values asspciated with that answer. I need those values to continue with the calculation. The problem I have now is what happens when my answer lies between 6.60 and 6.70, I need the interpolated values returned now. For example, for 6.67 the releavnt answer from column B should be 13817.3. I would like excel to do this automatically. I cannot write an interpolation formula as suggested previously as there are 80 rows and 12 columns of data so it would take me forever. I have tried the TREND function but the answer it returns is incorrect. It returns an answer in excess of 13886 which cannot possibly be right. "hellmouthpirate" wrote: I will try to explain this the best I can. I have to do a calcualtion. The answer to that calculation will then be used to look up data in a table. For Example A B C D 6.60 13657 178.3 70.20 67.79 6.70 13886 179.9 70.16 67.68 My calculated answer was 6.67. How do I write a formula so that excel will return the corresponding values for A B C & D for 6.67. I have tried but the VLOOKUP function but it keeps returning the lower values which is not what I want. Yours hopefully Simon |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Interpolation within a data table
Hello,
TREND is IMHO not the right way to go. If, for example, you would have A values of -1,000,000 for the points 6.5 and 6.8 then TREND through all points 6.5, ..., 6.8 would result in -493,126 for the three points 6.6, 6.67 and 6.7. If I understood you correctly you were looking for an interpolation. My formula is giving you exactly that. An interpolation UDF you can find he http://www.sulprobil.com/html/interpolate.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to do linear interpolation between entries in lookup table | Excel Discussion (Misc queries) | |||
Find a Value by interpolation from a table of x and y values | Excel Worksheet Functions | |||
Interpolate, Interpolation, VlookUp, HlookUp, Read a table | Excel Worksheet Functions | |||
help with interpolation and limit of interpolation | Excel Discussion (Misc queries) | |||
interpolation XY data with bezier curver | Charts and Charting in Excel |