Non-Linear interpolation
Dear ExcelExperts Admins and fellow Members,
I require your kind help to sort out a little issue I'm experiencing in interpolating from a table of given values.
My goal is to obtain a cell where I can input a value and the program gives me the corresponding interpolation value in the cell next to it.
To do so I initially tried to obtain a ratio between the minimum and maximum value and it was then that I found out that the change is not linear and the (although very small) difference produces an error too large for my purposes.
I then obtained an equation via a graph looking for values that I knew would still be wrong but hopefully acceptable. Even then I was unlucky, so I resolved that the only way is to produce a system that takes into account all the values and the corresponding variations between them.
In the file I have enclosed the table is in the second page and I draw also a graph in which the thin red line highlights the non linearity of the values. That is particularly evident in the bottom part but those are the most critical values so they can't be ignored just to obtain satisfactory results for all the other.
So finally it's important that Excel looks for the two closest values to the one I will input and produces the result accordingly.
Thank you for reading this far, any help will be highly appreciated!!
Ciao, good day to you,
Patrizio.
| Attachment | Size |
|---|---|
| Mass and Balance Challenger.xls | 11.5 KB |
hihi... this is a massive
hihi... this is a massive field involving many aspects. The ways of doing non-linear interpolation are huge.
Here's are the most common methods:
- cubic spline
- exponential
- log-linear
Potential issues:
- Does your line go through all the points ?
- what happens before the first point ?
- what happens after the last point ? (extrapolation)
- what happens between points ?
I suggest you use linear interpolation between points.
- it's simple
Take a look here also:
http://excelexperts.com/Finance-Interview-Questions#comment-169
Ciao Nick, Thank you for your
Ciao Nick,
Thank you for your contribution, in fact I watched the video it was very interesting but I realised it's not really what I need, because in fact I already have all the values!!
So I agree with you, a simpler linear interpolation between the points could be an acceptable solution.
How should I do it then!?
Thanks!!
Here's an example to kick you
Here's an example to start you off:
http://excelexperts.com/Excel-For-Finance-Tips-Interpolation
Ok, I feel like we're getting
Ok, I feel like we're getting there... I actually used this formula at the beginning but I was only able to set it between the limit values of 1000 and 16000 in my case.
Now though, how do I tell the program to interpolate between the closest two numbers I have in the table? for instance if I wanted to calculate the value at 3500 the program should automatically look at 3000 and 4000 rather than at 1000 and 16000 that are the limit values because if it did so it won't work... I mean the program should be smart enough to look at the best values from which he will produce the best interpolation.
try with a VLOOKUP... the
try with a VLOOKUP... the last argument shld be TRUE to tell the VLOOKUP to use an approximate match.
- that will return you the first point.
then add 1, and use vlookup again to find the next point