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.

Adding a line of best fit or Trendline to a Scatter Plot
Click on the graph to open the three boxes to the right of the graph.  Click on the one with a plus sign (+) and click on the arrow pointing right next to the option for trendline. Click More Options... to open the dialog box on the right.

 

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.

 

Linear Trendline and Equation for the Line of Best Fit
In the Trendline dialog box , choose linear next to the red asterisk (*) and, at the bottom, check “Display Equation on chart” and “Display R-squared value on chart” both are near the green asterisk (*). Once these are selected the graph should have a line that describes the trend for the dataset.  This is your STANDARD CURVE (blue dotted line on chart next to the red asterisk *) with the equation of the line (y= mx+b next to the green asterisk *).

 

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.

 

Determine the Concentration of the Unknown Sample
The line of best fit  equation is y=0.0926x+0.0742.  Rearrange the equation to solve for x; subtract 0.0742 from y and divide by 0.0962 in that order. The Excel syntax for the solving x is entered into B11 for y=0.67  =(0.67-0.0742)/0.0962. The equation can be found next to the  red asterisk in the function box. The result of the equation is in box B11, 6.4 mg/mL.

 

Exercises

Reveal the answer questions

What if your unknown sample had an absorbance value of 1.6? Could you still use this curve fit to obtain a good estimate of concentration?

NO. There are a couple of issues.

 

What could you do if your absorbance readings are outside the range of reliable data? Are you just out of luck? YES or NO

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.

 

In the learning activity you made a standard curve that explains the relationship between absorbance at 280 nm (A280) and the concentration of protein X. Use that standard curve (equation for the line) to determine the unknown concentration of protein X. You dilute sample in the previous question (A280=1.6) two fold and the measured absorbance is A280= 0.91. Use excel to calculate the result.

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).