Fitting data with Excel by HWQZ0H9

VIEWS: 4 PAGES: 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
 26                                      follow the prompts.
 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