Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & Extrapolation
I have the following columns
Va2 177 V3 216.37 V3/rootT3 11.984 Q3 ? V3/rootT3=11.984 which is not in the table I need to retrieve the Q3 from a long table looks as following V / Root.T Q 11.888 0.03450 11.924 0.03456 11.960 0.03463 11.996 0.03469 12.032 0.03475 As you can see 11.984 dose not exists I need to extrapolate between any tow columns in V / Root.T and return the answer Q to Q3 cell How can I do that? Please |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & Extrapolation
Hi,
I have no idea what Va2 or V3 has got to do with your question. Try these for looking up where no exact match exists. To find a lower value =VLOOKUP(D1,A2:B6,2,TRUE) To find the higher value =MIN(IF(A2:A6=D1,B2:B6)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "alawi" wrote: I have the following columns Va2 177 V3 216.37 V3/rootT3 11.984 Q3 ? V3/rootT3=11.984 which is not in the table I need to retrieve the Q3 from a long table looks as following V / Root.T Q 11.888 0.03450 11.924 0.03456 11.960 0.03463 11.996 0.03469 12.032 0.03475 As you can see 11.984 dose not exists I need to extrapolate between any tow columns in V / Root.T and return the answer Q to Q3 cell How can I do that? Please |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & Extrapolation
Hi,
Linear extrapolation? I don't have time to finish this right now but here is a start. If your known X's are in C1 and C2 and the new X is in C3 and the known Y's are in D1 and D2 =((C3-C1)/(C2-C1))*(D2-D1)+D1 =TREND(D1:D2,C1:C2,C3) You can combine this in an IF with VLOOKUP and possibly MATCH. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "alawi" wrote: I have the following columns Va2 177 V3 216.37 V3/rootT3 11.984 Q3 ? V3/rootT3=11.984 which is not in the table I need to retrieve the Q3 from a long table looks as following V / Root.T Q 11.888 0.03450 11.924 0.03456 11.960 0.03463 11.996 0.03469 12.032 0.03475 As you can see 11.984 dose not exists I need to extrapolate between any tow columns in V / Root.T and return the answer Q to Q3 cell How can I do that? Please |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & Extrapolation
Hello,
TREND is outright dangerous, if you apply it on all your points, for example: Enter into A1:B4 1 -100000 2 4 3 5 4 -100000 Then =TREND(B1:B4,A1:A4,2.5) would result in -49997.75 and not 4.5 Or its very complex to use because you need to find the two boundary points in your data set which you want to feed into TREND. I suggest to use my UDF Interp: http://www.sulprobil.com/html/interpolate.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extrapolation and Interpolation questions | Charts and Charting in Excel | |||
Extrapolation of non linear data | Excel Worksheet Functions | |||
extrapolation curves on excel | Charts and Charting in Excel | |||
Correcting an extrapolation macro | Excel Discussion (Misc queries) | |||
Automating Extrapolation | Excel Discussion (Misc queries) |