Skip to content

Excel Export

You can export TableCurve's Curve-Fit to an Excel XLS spreadsheet file. Use the MS Excel Export button in the control panel of the Curve-Fit graph or the Save Excel option in the File menu of the Curve-Fit graph. Three formats are offered varying from all curve-fit information to simple generated XY data.

XLS Format

Three Excel formats are supported:

  • Excel 2000/Excel 97
  • Excel 95
  • Excel v4

The Excel v4 format uses fixed columns, regardless of whether or not data are present. The newer Excel formats contain columns only when that type of data is present. The newer Excel formats also use a different column order and structure to be compatible with the Excel export offered in TableCurve 2D's automation facility.

Structure of Exported XLS

The Full Worksheet item writes all possible information to the worksheet including fit parameters, fit statistics, the original data with predicted values and confidence and prediction limits, and a generated section which contain X values determined by the values in the X Start, X Incr, and X End fields. This generated section contains the predicted Y value as well as confidence and prediction intervals. The initial values in the X generation will produce a block of 100 points. The confidence value used will be the value currently set in the Intervals option.

The Generated Information option writes only the generated columns, including the confidence and prediction limits.

The Generated XY Only option writes only the X,Y information defining the curve to the first two spreadsheet columns.

Equation Formula

The XLS worksheet produced in the Full Worksheet option can contain a generated Excel formula representation for the curve-fit equation. To enable absolute A1 cell addressing in a variable column export, the Excel 95 and Excel 2000/Excel 97 options place this formula in the G and H columns of the worksheet. For the Excel v4 format, the formula will be in the last column written in the sheet.

You must enter this cell for Excel to process the formula and you must copy this formula to all cells in the column for which you wish to compute a predicted Y value. The X values for this formula are entered in the preceding column. Note that this generated equation will contain only the native mathematical formula. There will be no conditional statements to manage undefined regions.

If Omit is checked, no formula is written. If Write Using A1 Cell Addressing is checked, the formula is written for specific cell addresses using the A1 format. If Write Using R1C1 Cell Addressing is checked, the formula is written for relative row-column addresses using the R1C1 format. The relative addressing is used in the Excel export produced by the automation facility when multiple export streams appear on each sheet. For the Excel v4 format, the options to omit the formula and select cell addressing will be grayed.

Note that no equation formula will be generated for Chebyshev and Fourier Series equations because it is not possible to generate a single-line formula for these models. Also, for Excel 95 and Excel v4 formats, no equation formula will be generated for equations containing greater than 11 coefficients. This is due to a formula length limit built into these versions of Excel.

Inserting BASIC Module in Excel

For later Excel versions supporting VBA (Visual Basic for Applications), you may want to add a BASIC module containing the curve-fit equation to the spreadsheet. Although it involves several steps, this approach has the advantage of producing a viable formula for all TableCurve 2D equations, including the Chebyshev and Fourier Series equations. For Excel 95 and prior, it also manages equations containing greater than 11 coefficients.

This approach makes the curve-fit function global to Excel just as if it were a built-in Excel function. Further, the BASIC code is generally easier to understand than the single line formula since the coefficients appear directly rather than as cell references.

Note that you can use this option even if you have no experience with BASIC because no language understanding is needed.

The necessary steps are as follows:

In TableCurve 2D:

  • Generate Function Code using Visual Basic or QBASIC, Function Code Only.
  • Generate the Excel file using this Excel export option

In Excel:

  • Open the generated Excel file
  • Tools/Macro/Visual Basic Editor to open the VB editor
  • File/Import File with the generated BASIC file name to add the VB module to the worksheet
  • Use the generated function name in formulas anywhere within the Excel sheets as in =eqn8010(A1)