XLInterp 4.0
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. This version supports both 32-bit and 64-bit versions of Excel. 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.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.
What's New in Version 4.0
- Added 64-bit add-in to work with 64-bit versions of Excel.
- Increased allowable table size to full worksheet size in Excel 2007 and later.
- Converted all help files to compiled HTML format.
What's New in Version 1.2
- Removed Help file link from Help Menu for Excel 2007 and later.
XLInterp 1.2 is available as a free upgrade to licensed users of XLInterp1.1
Key Features
- Functions can be used as if they were built in to Excel
- Treats data in your spreadsheet tables as points on curves or surfaces.
- Can find any point on the curves using non-linear or linear interpolation.
- Works with data tables of one or two dimensions.
- A two-dimension table can represent a surface function or a family of one-dimensional curves.
- Accepts variable grid spacing to allow greater accuracy in non-linear areas while minimizing data required for other areas.
- Accepts data in ascending, descending, or changing order.
- Performs table lookup in tables using labels for row or column headings.
- Includes both forward and reverse functions. This allows a single table of data to be used for all calculations.
- Includes functions for calculating partial derivatives.
- Supports 32-bit and 64-bit versions of Excel.
- Check pricing for attractive quantity discounts.
Functions
The following table lists all of the @Air functions currently available.
| Function(Input Arguments) | Type | Output Value |
| InterpRCT(table,row value,column value) | Forward | Value from table |
| InterpRTC(table,row value,table value) | Inverse | Column value |
| InterpCTR(table,column value,table value) | Inverse | Row value |
| InterpRCdTdR(table,row value,column value) | Forward | Partial derivative dT/dR)C |
| InterpRCdTdC(table,row value,column value) | Forward | Partial derivative dT/dC)R |
| InterpRTdTdR(table,row value,table value) | Inverse | Partial derivative dT/dR)C |
| InterpRTdTdC(table,row value,table value) | Inverse | Partial derivative dT/dC)R |
| InterpCTdTdR(table,column value,table value) | Inverse | Partial derivative dT/dR)C |
| InterpCTdTdC(table,column value,table value) | Inverse | Partial derivative dT/dR)C |
| InterpVer() | Version and serial number |
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.
| A | B | C | D | E | F | G | |
| 1 | 400 | 500 | 600 | 700 | |||
| 2 | 10 | 1240.58 | 1287.78 | 1335.55 | 1384.05 | ||
| 3 | 50 | 1234.95 | 1284.11 | 1332.92 | 1382.02 | ||
| 4 | 100 | 1227.36 | 1279.33 | 1329.57 | 1379.46 | ||
| 5 | 150 | 1219.10 | 1274.32 | 1326.14 | 1375.88 | ||
| 6 | 200 | 1210.13 | 1269.04 | 1322.61 | 1374.25 |
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
If you wish to try XLInterp, you may download a fully functioning copy for a 30 day evaluation period. If you decide to purchase XLInterp you may continue to use the download and we will provide you with a serial number that allows permanent use. Go to our Order page when you are ready to purchase XLInterp 1.1.