# Fitting a Quadratic in Microsoft® Excel

## Chapter 4, Printout 2

Note: Excel's Data Analysis tools have no built-in routine for fitting a polynomial. This example shows how to fit a quadratic using Excel's multiple linear regression tool to find y as a function of x and x2.
1. Enter the data.

• In cell A1, type "x"

• In cell B1, type "x^2"

• In cell C1, type "y"

• In cells A2-A19, enter the Ammonium Phosphate data from the x column in Table 4.3 (p. 133).

• In cell B2, type "=A2^2"

Screenshot:

• Select cells B2-B19.

• Select "Edit --> Fill --> Down."

Excel fills rows 2-19 of column B with the squares of the values in column A.

Screenshot:

• In cells C2-C19, enter the Compressive Strength data from the y column in Table 4.3.

Screenshot:

2. Find y as a function of x and x2 using multiple linear regression.

• Select "Tools --> Data Analysis."

• In the dialog that follows, select "Regression" from the list of options.

Screenshot:

• Fill out the resulting dialog box as follows:

Input Type Label Value
Input Y Range: \$C\$1:\$C\$19
Input X Range: \$A\$1:\$B\$19
Labels checked
Constant is Zero unchecked
Confidence Level unchecked
New Worksheet Ply: selected
New Worksheet Ply:
Residuals checked
Residual Plots checked
Standardized Residuals checked
Line Fit Plots unchecked
Normal Probability Plots checked

Screenshot:

• Click "OK."

Excel creates a new sheet in the workbook with the summary statistics, plots of residuals vs. x and x2, and a normal probability plot.

Screenshot (summary output):

Screenshot (plots):

Note: you can move the charts as necessary by clicking a blank area in the chart and dragging it to a new location. Other changes can be made by double-clicking various elements in the charts.