Exporting Nonlinear Regression from Polymath to Excel

See Also: Exporting from Polymath to Excel  Nonlinear Regression  Variables and Expressions 

The "Export to Excel"  capability in the Data Table is executed by clicking on the Excel icon found on the particular regression window.  You must have your Excel program active on your desktop before exporting a program to Excel.  A problem should be exported after it has been completely entered successfully into Polymath and ready for solution. It is also good practice to also solve the problem in Polymath so that the solution in Excel can be compared and verified.  Upon export, the problem in Polymath will be completely transferred into Excel.  This transfer automatically includes the translation of logical statements and the intrinsic functions from Polymath into equivalent functions within Excel. 

Nonlinear Regression

Example:  Calculation of the Parameters of the Antoine Equation
 

Consider the data set shown below that is from Example 2 - Vapor Pressure Data in the Polymath REG Regression Program. The data set may be obtained within the Polymath REG program by clicking on the Examples button and holding until Example 2 is highlighted.  This should bring the data into the Polymath Data Table. Note that the original data of TC and P have been transformed for regression purposes.

The Antoine equation is of the form

    logP = A + B/(TC + C)

where logP = log(P) and TC = T °C are predefined columns and A, B, and C are parameters.

A mouse click on the Nonlinear Regression tab on the right side of the Polymath Data Table should bring up the Nonlinear Regression window.  Entry of the problem into the Model using columns names from the Data Table and parameters A, B, and C is shown below.  Note that after the Model syntax is correct, then the Initial Guesses for the Model Parameter should be entered.

The Excel program must be open prior to the export of this Polymath program to Excel.  A single mouse click on the Excel icon   results in the automatic export and setup of the problem in a new worksheet in the open Excel workbook as shown below.

Note that the pertinent information from Polymath problem has been transferred including the particular problem variables from the Data Table, the values, and the regression equation.  The Sum cell, which is highlighted in yellow, is created from the sum of the squares of the errors between the independent variable, logP, and the calculated variable from the regression, logP calc.  This cell value should be minimized during the Excel solution.

The solution of the problem within Excel must utilize the "Solver" Add-In which is a part of the Excel software package, but you must be sure that it is installed.  This is accomplished from the Tools/Add-In menu of Excel where Solver should be checked.  The solution is initiated by going to Tools/Solver... and then filling out the Solver window as shown below:

Note that the "By Changing Cells:" input is set to the three cells containing the initial guesses of the parameters A, B, and C as specified by $H$4:$J$4 and can be entered with the mouse with standard Excel entry.   Pressing the Solve button gives the Solver solution.

Click "OK" to keep the Solver solution.  Note that this Excel solution with Solver gives the same regression result as is obtained with the Polymath program for this same problem. See Polymath Nonlinear Regression Example.