We all know that Microsoft Excel® is a great tool for automating engineering and scientific calculations.  Although Excel includes many powerful features, its table lookup functions are rather limited. They might be adequate for the average user, but the technical user needs something more powerful. To the technical user, data in a table often represents points on a curve rather than just a collection of information. We use curves to represent all types of information such as thermodynamic properties, transport properties, material properties, equipment performance, correction curves, efficiencies; the list is endless.  If your table represents points on a curve, Excel’s built-in lookup function will only return the value from a specific point in the table.  What we really need are functions that can find the value of any point along the curve, even if it falls between two points in the table.

Techware’s XLInterp provides the solution to this problem.  This add-in includes a set of nine new functions, which can extract just about any type of information from your spreadsheet tables.  These functions use two types of interpolation to analyze the data in your table, linear and non-linear interpolation. The linear method finds two points in the table that bracket your input value, constructs a straight line through those points and finds the value along that line. The non-linear method uses two additional points, constructs a third order polynomial curve through the four points and finds the value along that curve. In most cases you will want to use the non-linear method because it gives the best results for smooth curves.

The XLInterp functions work with two-dimensional tables so that they can be used to analyze data with two variables.  In addition, XLInterp includes both forward and inverse functions.  With the forward functions, you provide the variable(s) and it finds a value on the curve (or surface for two-dimensional problems.) With the inverse functions, you provide a point on the curve (or a variable and a point on the surface for two-dimensional problems) and it finds the unknown variable.

XLInterp has a subtle feature that greatly enhances its power; its functions can work with non-numeric column and row labels.  When non-numeric data is used for either the row or column values, the functions do not interpolate in that direction. Instead, they search for a label that matches the input variable and then interpolate along that row or column.  This feature enables you to assemble tables which represent collections of one-dimensional curves that you can access by name.

XLInterp also includes advanced functions, which calculate partial derivatives of the curves represented by the data in your table. You can calculate derivatives with respect to row or column variables from either forward or inverse functions.

Key Features

XLInterp Functions

How Does It Work?

A Simple Example Illustrating the Ease and Power of XLInterp

The spreadsheet table below named TABLE1, defined as cells (A1..E6), represents a function of two variables. To make this example meaningful, the table actually represents the enthalpies of steam as a function of pressure and temperature. The numbers in row 1 represent temperatures in degrees F, while the numbers in column A represent pressures in psia. The cells in the area (B2..E6) hold the enthalpy values of steam in Btu/lb.

Suppose we are interested in determining the enthalpy of steam at a pressure of 60 psia and a temperature of 440 deg F. The formula =InterpRCT(TABLE1,60,440,0) entered in any cell finds the value of the function described in the table using linear interpolation. In this example, the value returned is 1253.32, rounded to the nearest hundredth. If the last argument in the formula is changed from a 0 to either a 1 or an "NL", the function uses non-linear interpolation and returns a value of 1253.48. It is interesting to note that the ASME steam tables lists the enthalpy of steam at 60 psia and 440 deg F as 1253.5 Btu/lb, rounded to the nearest tenth, and Techware’s WinSteam function returns a more precise value of 1253.51. Even with this coarse table, the non-linear function returns an excellent result.

Try It