1 / 5

Linear Regression Analysis Using MS Excel

Linear Regression Analysis Using MS Excel. Making a graph Analyzing Data. This is a standard curve for a protein assay. You will use Excel to create this type of chart. Absorbance of Unknown. Concentration of Unknown.

lane-mendez
Download Presentation

Linear Regression Analysis Using MS Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Linear Regression Analysis Using MS Excel • Making a graph • Analyzing Data

  2. This is a standard curve for a protein assay. You will use Excel to create this type of chart. Absorbance of Unknown Concentration of Unknown

  3. Enter your chart title as well as titles for the X-axis and Y-axis. Then press the Gridlines tab. Uncheck the Major Gridlines button. Most scientific charts do not use them. Press the Legend tab. This is the Chart Wizard. Select XY (scatter) as the chart type and highlight the markers only as the chart sub-type. Press the Next button. Input your X data into column A and your Y data into B. Include your labels. Verify that the your data is in columns. Press Next. Highlight the labels and the data from the standard values. Do not include data from the unknown sample. Use the Insert menu and select Chart. Uncheck the Show Legend button to remove the legend box. You would want to use a legend if you have more than one data series. Press the Next button. Check As New Sheet to insert your chart into a new sheet. Press Finish. This is your graph. You must now insert the trendline, the calculated line of best fit. (Click mouse to advance.)

  4. Highlight your data points using the left mouse button. Once highlighted, use the right mouse button to view Data Series Format menu. Select Add Trendline to add trendline. Select Linear as the Trend/Regression type. Then press the Options tab. Check the Display Equation and the Display R-squared value buttons to insert these onto your chart. Press the OK button to display chart. You now have your chart showing the data points that you measured and the line of best fit (the trendline). (Click mouse to advance.) Return

  5. While it is possible to estimate the concentration of the unknown using the chart with the standard curve, it is preferable (and more accurate) to use the calculating power of Excel to determine this value. On your chart, use the trendline equation to determine the concentration of the unknown sample. In this example, the equation is: y = 0.0045x + 0.0024 To calculate the concentration, substitute the absorbance of the unknown sample for y in the equation and solve for x, the concentration. Since the absorbance of the unknown sample was 0.345, the trendline equation will be: 0.345 = 0.0045x + 0.0024 Solving for x: x = (0.345 - 0.0024) / 0.0045 x = 76.1 mg The unknown sample contained 76.1 mg of protein. .

More Related