When you have graphed the scatter plot in Excel, the next step is to add a line of best fit or “Trendline”. If you are using Excel Online you will need to click EDIT IN EXCEL (right side of the grey stripe across the top). In Excel (“off” line) you can make edits to the graph. See the figure for instructions on how to open the “Trendline” options.
We know that in spectrophotometry protein concentration and absorbance at 280 nm have a linear relationship (see the previous learning activity). Excel uses the general term “Trendline” which includes linear and nonlinear relationships in data. Now that you have opened the “Format Trendline” dialog box to the right, you can select the “Linear” option. We will also need the equation for the line of best fit and the R2 value which are described below. See the figure below to select all three options in Excel.
Definition:
Equation for a line - written as y=mx+b where m is the slope and b is the y-intercept for two or more data points plotted on a graph with x and y axes.
The equation on the graph describes the trend between absorbance and concentration of protein X, based on our four known samples. Note that the line doesn’t exactly go through every data point, but instead is a truly straight line. All data have some degree of error. The curve-fitting procedure Excel carried out determines the line that minimizes the total distance between the line and data points, while weighing each data point equally. We won’t take you through the math because excel is programed to do it for you. The equation, which should be y = 0.0926x + 0.0742. The same principle can apply to any experimental standards with know x and y values. Not all trendlines are linear. In the exercises below, you will work with non-linear standard curves.
The R-squared value is displayed on your graph under the equation (next to the green asterisk *) and tells you how good the fit of the line is. If the equation of the line were a perfect fit, going through every point exactly, the R-squared value would be 1.0. An R-squared value of zero would mean that the Y values do not have any correlation with the line. You should have an R-squared value of 0.988, which reflects a very good fit.
The next step is to solve the above equation for x (concentration) after plugging in the unknown sample absorbance value (0.67) for y. In the figure below you can see the Excel syntax next to the red asterisk (*) to solve for x.
Exercises
Reveal the answer questions
NO. There are a couple of issues.
No. All you would need to do is dilute the unknown sample, so that you obtained a value in the useable range. Given that the undiluted sample gave 1.6, a 2x dilution (1 part sample + 1 part solvent) would likely put you somewhere around 0.8, which would be an acceptable reading.
To calculate the concentration in Excel use the standard curve equation for the line (y=0.0926x+0.0742). If y=0.91 and you need to solve for x the syntax in Excel is: =(0.91-0.0742)/0.0926. The result of the equation is 9.0 mg/mL. Because you performed a 2x dilution of the protein solution you should multiply this result by two to get the undiluted concentration. 2x=18 mg/mL (Link to figure).