Model Data Using Regression and Curve Fitting

This example shows how to execute MATLAB® data regression and curve fitting in Microsoft® Excel® using a worksheet and a VBA macro.

The example organizes and displays the input and output data in a Microsoft Excel worksheet. Spreadsheet Link™ functions copy the data to the MATLAB workspace and execute MATLAB computational and graphic functions. The VBA macro also returns output data to a worksheet.

To work with VBA code in Excel with Spreadsheet Link, you must enable Spreadsheet Link as a reference in the Microsoft Visual Basic® Editor. For details, see Installation.

Open the ExliSamp.xls file and select the Sheet1 worksheet. For help finding the ExliSamp.xls file, see Installation.

Sheet1 of the spreadsheet contains the named range DATA, which consists of the example data set in worksheet cells A4 through C28.

Sheet1 contains the code for Spreadsheet Link functions in the cells of column E.

Model Data in Worksheet

To perform regression and curve fitting, execute the specified Spreadsheet Link functions in worksheet cells.

  1. Execute the Spreadsheet Link function that copies the sample data set to the MATLAB workspace by double-clicking the cell E5 and pressing Enter. The data set contains 25 observations of three variables. There is a strong linear dependence among the observations. In fact, they are close to being scalar multiples of each other.

  2. Execute the functions in cells E8, E9, and E10. The Spreadsheet Link functions in these cells regress the third column of data on the other two columns, and create:

    • A single vector y containing the third-column data

    • A three-column matrix A, which consists of a column of 1s followed by the rest of the data

  3. Execute the function in cell E13. This function calculates the regression coefficients by using the MATLAB back slash (\) operation to solve the overdetermined system of linear equations, A*beta = y.

  4. Execute the function in cell E16. MATLAB matrix-vector multiplication produces the regressed result, fit.

  5. Execute the functions in cells E19, E20, and E21. These functions:

    1. Compare the original data with fit.

    2. Sort the data in increasing order and apply the same permutation to fit.

    3. Create a scalar for the number of observations.

  6. Execute the functions in cells E24 and E25. Fit a polynomial equation to the data for a fifth-degree polynomial. The MATLAB polyfit function automates setting up a system of simultaneous linear equations and solutions for the coefficients. The polyval function then evaluates the resulting polynomial at each data point to check the goodness of the fit newfit.

  7. Execute the function in cell E28. The MATLAB plot function graphs the original data (blue circles), the regressed result fit (dashed red line), and the polynomial result (solid green line).

    Figure window contains a graph of original data, regressed result, and polynomial result.

    Since the data is closely correlated, but not exactly linearly dependent, the fit curve (dashed line) shows a close, but not exact, fit. The fifth-degree polynomial curve newfit is a more accurate mathematical model for the data.

Model Data Using VBA Macro

To model the data using a VBA macro, execute the Spreadsheet Link functions in a VBA macro.

  1. In the ExliSamp.xls file, click the Sheet2 tab. The worksheet for this example appears.

    Worksheet cells A7 through A31 are empty cells for the y variable, cells B7 through B31 are empty cells for the fit variable, and cells C7 through C31 are empty cells for the newfit variable.

    Cell A4 calls the macro CurveFit, which you can examine in the Microsoft Visual Basic environment.

    ExliSamp.xls - Module1 (Code) window contains the VBA code for the CurveFit function with arguments aData, sTarget1, sTarget2, and sTarget3.

    While this module is open, ensure that the Spreadsheet Link add-in is enabled. To enable it, see Add-In Setup. After the add-in is enabled, the Project Explorer lists it under the References folder.

  2. Execute the CurveFit macro by double-clicking the cell A4 and pressing Enter. The macro runs the Spreadsheet Link functions. When the macro finishes, the input and output data appears in worksheet cells A7:C31.

    • Column A contains the original data y (sorted).

    • Column B contains the corresponding regressed data fit.

    • Column C contains the polynomial data newfit.

    Worksheet cells A7 though A31 contain the data for the y variable, cells B7 through B31 contain the regressed data for the fit variable, and cells C7 through C31 contain polynomial data for the newfit variable.

See Also

| | | | |

Topics