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:
http://www.blueleafsoftware.com/Products/Dagra/LinearInterpolationExcel.php
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 ( http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm ), however it is not compatible with Excel 2007 or higher versions. 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 (http://xongrid.sourceforge.net/) 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 http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx. 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.
Absolutely fantastic Mr. Landry has done this for other users. Have been missing the ease of interpolation that used to be provided by XLXtrFun.
ReplyDeleteCouldn't for the life of me get it working on Excel 2013 64-bit though. Tried everything.
The previous version, Version 3 works with Excel 2013 64-bit, for some reason the latest version doesn't. You can find the earlier version on the sourceforge site above under files.
DeleteThis comment has been removed by the author.
ReplyDeleteI'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.
ReplyDeleteThe kriging interpolation in XonGrid may be more accurate, however.