# Fitting data with Excel by HWQZ0H9

VIEWS: 4 PAGES: 1

• pg 1
```									Susan Piepho                                                                                                                    11/29/2011
80fd94cc-284d-4e57-91c4-69a8ef91620d.xls                     Fitting Data with Excel

A                   B         C            D           E           F               G        H            I          J
1                                                           Procedure to Graph Data plus Trend Line
2     x data               y data
3                                      Arrange your data as shown (with x values at the left) or see the note in the
4    conc (M)                   A      paragraph below. Choose the Chart Wizard icon from the top toolbar. Select the
5     0.010                    0.10    XY(Scatter)graph style, and the default Chart subtype (Scatter).
6     0.020                    0.19
7     0.030                    0.32    When prompted for the Data range, click in the data range box and select your
8     0.040                    0.38    data (here the shaded squares at the left, cells A4 through B10, including the
9     0.050                    0.55    titles!). NOTE: If your x data is to the left but is in a non-adjacent column, begin
10     0.060                    0.59    by selecting the column with the x values (including the title); then hold down the
11                                      CTRL key (Daisy key on Mac) as you select the column with the y values
12                                      (including the title).
13
14                                      When prompted, add a title and labels for the x and y axes. At the Place chart
15                                      prompt, choose as object in Sheet 1. Then select the Finish button.
16
17                                      To add a trend line (least squares fit line), begin by clicking the series of data
18                                      points in your graph (they should become highlighted); when you do this the set of
19                                      menus at the top of the screen will change. Go to the Chart menu and choose
20                                      Add Trendline. Hit the Type tab and choose Linear as the Trend/Regression type.
21                                      Next hit the Options tab and check the boxes at the bottom of the screen labeled
22                                      Display equation on chart and Display r-squared value on chart. Then hit the OK
23                                      button.
24
25                                      To change features of the graph, click on the feature you wish to change and
27
28
For example, in this case the trend line equation came up initially with too few
29
significant figures. Thus I selected the trendline equation above the graph and
30
double-clicked on the boundary of the resulting selection box. This brought up
31
Format Data Labels. I then clicked on the Number tab and choose scientific with 3
32
33
34
Beer's Law Plot
35                                       slope = 10.3, y-intercept = -0.00400
36
37                   0.70
38                                     y = 1.026E+01x - 4.000E-03
39
0.60                     R² = 9.827E-01
40
0.50
41
Absorbance

42                   0.40
43
44
A
0.30
45
46                   0.20
47
48                   0.10
49
50                   0.00
51                      0.000          0.020         0.040          0.060           0.080
52
Concentrtion (M)
53
54

```
To top