MS Excel is a very useful software program and it has many built in functions. We often hear, ' How do I interpolate in Excel, where is the interpolation function?' As many functions as Excel has, it still lacks a simple function to perform quick interpolations. Yet, it should be stated that it is possible to pair up functions (e.g., using forecast, offset, and match functions) to basically performance the interpolation as illustrated here: However, this method is not as intuitive as having a simple build in function. There is an XLL file that you can add-in to Excel entitled XlXtrFun ( ), however it is not compatible with Excel 2007 or higher versions. Actually you have a 3-d table - x and y are independent variables, and the one you need to find is z. I know a solution for 2-d table, but for 3-d it should be similar. So if you have 2-d table, you can find out which formula fits best your data, using Excel's 'trend line' feature. Furthermore, at the time of this post it is not possible to download the zipped file from the above website, which makes XlXtrFun a non-viable option. Which brings us to the motivation for this post: XonGrid Interpolation AddIn () is a freely available add-in for Excel which works for both the 32 and 64 bit versions of Excel and is also compatible with the older and newer versions of Excel (e.g., Excel 2003, 2007, 2013) unlike XlXtrFun. While XlXtrFun has many more functions than XonGrid, if you are looking for Excel interpolation add-in functions, XonGrid is perfect for you. As a nice bonus to improve computations if you are doing many simulations interpolations of the same basic dataset, the XonGrid interpolation functions can be used as part of array functions, as mentioned in. Instead of using the sum function mentioned in the above article, you can use the interp1d function as part of the XonGrid package. Be sure to fix the data range with the $ characters, so that when the array function is populated in each cell, the data range is not changed, only the xo value change which will corresponded to the desired yo value that you find. I've had no problem getting XlXtrFun to load as an Add-In in the 32-bit versions of Excel 2013 and 2016. I couldn't get it to work with the 64-bit versions. I first put XlXtrFun.xll in C: Program Files Microsoft Office Office16 XLSTART (You may need to create this folder; other folder locations may work as well). Then from within Excel I went to File -> Options -> Add-Ins -> Manage - Excel Add-ins, navigated to the folder where I had put XlXtrFun.xll, and added it. After restarting Excel, it worked. The kriging interpolation in XonGrid may be more accurate, however. Recently, I received the following question from a reader: “I have an Excel question – Is there a way to interpolate a value from a table? I have an X and Y that are not on the table, but have correlated data so want to calculate the interpolated value”. Interpolation estimates data points within an existing data set. As a simple example, if it took 15 minutes to walk 1 mile on Monday and 1 hour to walk 4 miles on Tuesday, we could reasonably estimate it would take 30 minutes to walk 2 miles. This is not to be confused with extrapolation, which estimates values outside of the data set. To estimate that it would take 2 hours to walk 8 miles would be extrapolation as the estimate is outside of the known values. Excel is a great tool for this type of analysis, as ultimately it is just a big visual calculator. In terms of answering my reader’s question, there are a number of scenarios which would lead to different solutions. At first, I thought I could just use simple mathematics. This would work if the results were perfectly linear (i.e., the values move perfectly perfect sync with each other). But what if they are not perfectly correlated? I then thought about Excel’s FORECAST function. Based on its name, the FORECAST function seems like an odd choice. It would appear to be a function specifically for extrapolation, however it is one of the best options for linear interpolation in Excel.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2019
Categories |