Friday, October 25, 2013

Flowing Fix: Interpolation in MS Excel using XonGrid Interpolation AddIn

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.

Preventing the Java Updater from including the Ask Toolbar in the installer

The Java Updater has an annoying habit of prompting the user to install the Ask.com Toolbar. This unwanted behavior occurs every time a Java update is released. We found a solution to this that involves modifying the registry, and wrote this small application that does the registry modification for the user. To use it, right-click on the program and select Run as Administrator. Please send any questions to noberg at illinois dot edu. Download link: EXE or ZIP [16 KB]. The source code (Visual Studio 2010, C# project) is also available as a ZIP.