Modeling Data Sets Using Data Regression and Curve Fitting :: Solving Problems with the Spreadsheet Link EX Software (Spreadsheet Link™ EX)

Modeling Data Sets Using Data Regression and Curve Fitting

On this page…
Using Worksheets Using Macros

On this page…

Regression techniques and curve fitting attempt to find functions that describe the relationship among variables. In effect, they attempt to build mathematical models of a data set. MATLAB matrix operators and functions simplify this task.

This example shows both data regression and curve fitting. It also executes the same example in a worksheet version and a macro version. The example uses Microsoft Excel worksheets to organize and display the data. Spreadsheet Link EX functions copy the data to the MATLAB workspace, and then executes MATLAB computational and graphic functions. The macro version also returns output data to an Excel worksheet.

Using Worksheets

Click the Sheet1 tab on the ExliSamp.xls window. The worksheet for this example appears.

The worksheet contains one named range: A4:C28 is named DATA and contains the data set for this example.
Make E5 the active cell. Press F2; then press Enter to execute the Spreadsheet Link EX function that copies the sample data set to the MATLAB workspace. 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.
Move to cell E8 and press F2; then press Enter. Repeat with cells E9 and E10. These Spreadsheet Link EX functions regress the third column of data on the other two columns, and create the following:
- A single vector y containing the third-column data.
- A three-column matrix A, that consists of a column of ones followed by the rest of the data.
Execute the function in cell E13. This function computes the regression coefficients by using the MATLAB back slash (\) operation to solve the (overdetermined) system of linear equations, A*beta = y.
Execute the function in cell E16. MATLAB matrix-vector multiplication produces the regressed result (fit).
Execute the functions in cells E19, E20, and E21. These functions do the following:
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.
Execute the functions in cells E24 and E25. Often it is useful to fit a polynomial equation to data. To do so, you would ordinarily have to set up a system of simultaneous linear equations and solve for the coefficients. The MATLAB polyfit function automates this procedure, in this case for a fifth-degree polynomial. The polyval function then evaluates the resulting polynomial at each data point to check the goodness of fit (newfit).
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). It also adds a legend.

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

When you finish this version of the example, close the figure window.

Using Macros

Click the Sheet2 tab on ExliSamp.xls. The worksheet for this example appears.
Make cell A4 the active cell, but do not execute it yet.
Cell A4 calls the macro CurveFit, which you can examine in the Microsoft Visual Basic environment.
While this module is open, make sure that the Spreadsheet Link EX add-in is enabled.
- If you are using the Excel 2003 software:
  1. Click Tools > References.
  2. In the References dialog box, make sure that the excllink.xla check box is selected. If not, select it.
  3. Click OK.
- If you are using the Excel 2007 software:
  1. Click the Microsoft Office Button, .
  2. Click Options. The Excel Options pane appears.
  3. Click Add-Ins.
  4. From the Manage selection list, choose Excel Add-Ins.
  5. Click Go. The Add-Ins pane appears.
  6. Make sure that the Spreadsheet Link EX 3.0.1 for use with MATLAB check box is selected. If not, select it.
  7. Click OK to close the Add-Ins pane.
  8. Click OK to close the Excel Options pane.
In cell A4 of Sheet2, press F2; then press Enter to execute the CurveFit macro. The macro does the following:
1. Runs the same functions as the worksheet example (in a slightly different order), including plotting the graph.
2. Calls the MLGetMatrix function in the CurveFit macro. This macro copies to the worksheet the original data y (sorted), the corresponding regressed data fit, and the polynomial data newfit.