Least Squares Curve Fitting with Excel:
Ken Cheney 1/23/2002 .1
To return to Excel use the browser “back” or click on the Excel icon at the bottom of the screen.
Contents:
- Programs available
- To use the Least Squares Programs
- Object of least squares curve fitting
- Entering your data:
- Using “Solver” to solve for the coefficients:
- Plots (Charts)
- To add new data to an existing chart
- To change the look of almost anything on a chart
- About the “Excel Least Squares DHM” program
- Equations
- What you can solve for among R, k, and m
- About the “Excel Least Squares FHM” programs
- Equations
- To display formulas
- About “Names”
- Why names are good
- How to name a ceil or a range of cells
- How to change the name
- Making your own Plots (Charts)
- Making charts from data
- Adding least squares lines (Regression lines) and equations to charts.
- View multiple sheets at the same time
- Page breaks
Programs available:
Excel Least Squares DHM: for Damped harmonic Motion
Excel Least Squares FHM Amplitude: for Forced Harmonic Motion
Excel Least Squares FHM Phase: for Forced Harmonic Motion
Excel Least Squares Polynomial: Excel can do this better, this is just a test / demonstration
Excel Least Squares EXP: y=A + B e^C*x
To use the Least Squares Programs:
Object of least squares (regression) curve fitting:
Given a set of experimental x, y points and a theoretical formula relating x and y (say y=A+Bx+cos(Cx) ) the object of curve fitting is to find the values of the coefficients A, B, and C that bring the theoretical curve closest to the experimental data.
“Closest” is usually defined as minimizing the sum of the squares of the y differences between theory and experiment for each experimental x, hence “least squares”.
If there are error estimates (standard deviation of the mean) for the y values the y differences can be divided by the error estimate before squaring to produce a “weighted least squares” fit. With this procedure badly determined points do not influence the results as much as points with small error estimates. This procedure hasn’t been implemented here yet.
These programs use Excel to construct a sum of squares as outlined above, then Excel’s “Solver” is used to adjust the coefficients to minimize the sum of squares.
Entering your data:
* Enter your x and y data in the x and y columns (black on gray area), as many data pairs as you wish
* Enter the number of data pairs in the white on black area. "Number of data points n:"
* Enter your best estimates for the coefficients to be solved for in the white on black area
under "Coefficients / Names"
* If your data goes below the original data you need to copy the formulas for your data. .
* Select the original bottom row, all the way across the spreadsheet.
* Drag down on the little box at the lower right corner to get formulas for all your data
The rightmost cell may be 1 instead of 0, change it to 0. !!!!!!!! ????
* To include all the new data in the "Least Squares" fitting:
* Click on the number under "Sum", white on gray, in the box above the spreadsheet change the array to match your data.
- e.g. SUM G9:G22) might become SUM (G9:G54). Touch "Enter".
Using "Solver" to solve for your best coefficients:
* Click on "Tools" on the top menu bar. Click on "Solver".
You may not have to change anything but the following may be helpful:
* " Set Target Cell: Equal to:" 0
* " By Changing Cells": enter the coefficients you want adjusted. See about names and DHM below.
Be sure to get this right, otherwise you will change the wrong coefficients!
* Click "Options"
* Change "Derivatives" to "Central", this will give more accurate results. The calculations will be slower, you'll never notice.
* Estimates "Quadratic" also slower but more accurate.
* You may want to make "Precision" smaller to get a more accurate result.
* Click "Solve".
* That's it. The best fit for the Coefficients will have replaced your guesses.
Please excuse all the unjustified decimal places. I don't know how many significant figures you have.
You might select the columns (J say) and use "Format | Numbers" to make it more realistic!
To see the instructions while you work either print out the instructions or follow the section below:
Plots (Charts)
* A plot (Chart) will be on the "Plot__Data and Theory" sheet, see the tabs lower left.
You will have to add or remove data, see instructions below.
* A "Difference" or "Residual" plot will be on the "Plot__Difference" sheet.
These charts will probably not include the new range of data, to get your data see the next section.
To Change the range of data in a chart: i.e. when you add or change the data
* Click on the tab for the chart you want to change.
* Click on "Chart" on the top toolbar
* Click on "Source Data" on the menu
* Click on the little box with the grid at the right of "Data range:"
This should return you to the calculation sheet, if not click the "calculation" tab
* Select the data you want on the chart, include column labels. You should get a flashing dashed border!
* Click on the little grid in the "Source Data - Data range" box floating near the top of the spread sheet -
This should return you to "Source Data"
* Click "OK"
* Now you should have your new data on the old chart!!
To change the look of almost anything on a chart:
Right click on whatever you want to change and experiment!
About Damped Harmonic Motion: “Excel Least Squares DHM”:
m(d2x/dt2 ) + R(dx/dt) + kx=0 or L(d2q/dt2) + R(dq/dt) + q/C = 0
The mechanical solution for x is:
x=Amplitude * EXP(-R/(2*m) *t) * cos(frequency*t + Phase)
frequency=SQRT(k/m - (R/(2*m))^2)
Where x^3 means x cubed, EXP(x) means e^x and SQRT(x) is the square root of x
The electrical solution is the same when: m becomes L and k becomes 1/C.
Excel does not like R for a NAME so Resistance is spelled out.
About Resistance, k, and m: These do not occur separately but only as R/2m,
and k/m. This means that there are many values or R, k, and m that give the same values for
these two ratios. One of the three must be fixed!
To have m fixed, i.e. so Solver doesn't adjust it. Have solver change Amplitude, Resistance, k, and Phase
but not change m.
You do this when you run Solver: Tools | Solver | "By Changing Cells"
You type in Amplitude, Resistance, k, Phase
To have k fixed have solver change Amplitude, Resistance, m, and Phase.
Why SQRT(ABS(? Solver tries many values of the coefficients, some may
give a negative inside the square root which will stop the program.
About Forced Harmonic Motion: Amplitude and Phase:
“Excel Least Squares FHM Amplitude” and
“Excel Least Squares FHM Phase”
m(d2x/dt2 ) + R(dx/dt) + kx=Force* cos(frequency*t ) or L(d2q/dt2) + R(dq/dt) + q/C = Voltage * cos(frequency*t)
The mechanical solution for x is: x=(F/(frequency*Z)) * cos(frequency * t -Phase)
Z=SQRT(R*R+(frequency*m-k/frequency)^2)
Phase=90o +tan-1((frequency*m-k/frequency)/R) this can be written several ways!!
The steady state amplitude is F/Z
The electrical solution is the same except m becomes L and k becomes 1/C.
To display formulas:
Tools | Options | View | Formulas. Or Ctrl +` " ` " is at the upper left of the keyboard!
About "Names":
A cell or group of cells can be referred to by a name rather than $B$5 or $C15.
The advantage is that the formulas using these cells are much easier to read.
For example in "Least Squares DHM" the Theory formula is:
Amplitude*EXP(-Resistance/(2*m)*t)*COS(SQRT(ABS((k/m)-(Resistance/(2*m))*2))*t+Phase)
Resistance, m, t, k, and Phase are all names that would otherwise by referenced by $B$15 for m etc.
t is actually a range of cells under the heading: "time s".
To name a cell or a range of cells
1. Select the cell, range of cells, or nonadjacent selections that you want to name.
2. Click the Name box at the left end of the formula bar.
3. Type the name for the cells.
4. Press ENTER.
To change the name for a reference, formula, or constant
1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook list, click the name you want to change.
3. In the Names in workbook box, select the name.
4. Type the new name for the reference, and then click Add.
5. To delete the original name, click the original name, and then click Delete.
1.-5. Are pasted in from Excel's help! I generally have to blunder around a bit before I succeed in changing names!
Change data series names or legend text
To change legend text or data series names on the worksheet,
click the cell that contains the data series name you want to change, type the new name
To change legend text or data series names on the chart, click the chart, and then click Source Data on the Chart menu.
On the Series tab, click the data series names you want to change.
In the Name box, specify the worksheet cell you want to use as the legend text or data series name.
You can also type the name you want to use.
If you type a name in the Name box, the legend text or data series name is no longer linked to a worksheet cell.
Plotting and Charts: to make your own
Put your data in columns, x first then one or more y columns. Rows may work too.
Select the data you want plotted. The rows or columns must be adjacent.
Well, you can hold down the Ctrl key while selecting but it is much easier to make a new column where you want it by referring to the old column.
Insert | Chart | XY (Scatter) |Select the box matching what you want..
* Generally select the "XY (Scatter) from the menu at the left.
* Then select the box with smooth lines through points on the right.
* Later you will click on the line through the data points and eliminate the line.
* Right click on the line through the data points
* Format Data Series | Patterns | Line | None
Generally do NOT choose “Line” for the chart type. Select xy (Scatter).
If you select “Line” Excel will use regular spacing on the x-axis and your x values will only be used for labels! Weird!
Now you can enter title, labels for the axis, types of gridlines . . .
To format lines and markers just right click on them and then use “Format”. Notice you can choose color, line thickness, . . .
To format the background, grid, . . . Right Click on any open space on the chart and follow the menus.
To add regression (least square) lines to existing chart:
This will give faster, easier, more accurate results than using the "Do it yourself" least squares with Solver.
The downside is that not many functions are available.
With Solver you can construct almost any function.
Select chart if it is not on the screen.
Insert|Chart | Add Trend line| select the appropriate type. E.g. polynomial, linear, log, . . .
Click the “Options” tab, check off the boxes for “Display equation on chart” and “Display R-squared value on chart”
Smooth the angles of line charts
When you use this procedure to soften the jagged edges of a line chart, your data is not affected.
1. Click the line data series you want to smooth.
2. On the Format menu, click Selected Data Series, and then click the Patterns tab.
3. Select the Smoothed line check box.
View multiple sheets or workbooks at the same time
1. Open the workbooks you want to view.
To view multiple sheets in the active workbook, click New Window on the Window menu.
2. On the Window menu, click Arrange.
3. Under Arrange, click the option you want.
To view sheets in only the active workbook, select the Windows of active workbook check box.
Tip To restore a workbook window to full size, click Maximize
at the upper-right corner of the workbook window.
To specify how you want to plot the data in the chart, click Paste Special on the Edit menu,
and then select the options you want.
Start a new page by inserting a page break
1. Click the cell immediately below and to the right of where you want to start a new page.
If you click a cell in row 1, Microsoft Excel will insert only a vertical page break.
If you click a cell in column A, Microsoft Excel will insert only a horizontal page break.
If you click any other cell on the worksheet, Microsoft Excel inserts both a horizontal and a vertical page break.
2. On the Insert menu, click Page Break.
C:\KEN\WP\HANDOUTS\STUPROG\Excel Least Squares Help.doc Ken Cheney 1/14/19 3:32 PM Page 1