Most of the data that you will deal with in this course can be described using a straight line. This is especially true for calibration curves. Excel has a feature that allows you to easily display a linear trendline of a best-fit line through your data. This feature also allows you to view the equation of the best-fit line, and the correlation coefficient, both of which are determined using linear regression analysis. The trendline feature is helpful to quickly test the linearity of your data. A more complete treatment of linear regression is left for a later section.
A calibration curve is an equation that permits us to calculate a
desired experimental result in terms of another. In the simplest form,
this is given as the equation for a straight line, where the x-value
is the input and the y-value is the output. This is a
best-fit curve through a series of experimental sample data, and is the
series of all points that are average {x,y} pairs of
data, for the range of x and y, 
. Once
we know the equation for the average line, we can determine how well it
fits the actual experimental data, using the product-moment correlation
coefficient, or, for simplicity, the correlation coefficient, R.
This is a measure of how close the data points are to the line. If the
correlation coefficient is ±1, it is a perfect fit and the line
accurately describes the data. An R of 0 indicates no linear
correlation, and the straight line does not describe the data at all.
An |R| value close to 1 is desirable. The sign of R
indicates the slope of the regression line. The square of the
correlation coefficient, R2, is also a common
measure.
The simplest way to determine a calibration curve from plotted data in Excel is to use the Trendline option on a chart. From this you can view the best-fit curve line and display the equation and correlation coefficient. While this does not provide accurate information, it is a good first test of the correlation. To display a trendline on a plot on the fluorescence:
- Plot the fluorescence intensity data on a chart, with the concentration on the x-axis and the fluorescence intensities on the y-axis.
- On the plot, highlight the data series of interest and press the right mouse button. This will open a context-sensitive menu.
- Select Add Trendline…and dialog box will appear.
- In the Type tab, select Linear in the Trend/Regression Type box and press Ok. A trendline will appear on your plot, which is the best-fit line through the data points.
You can display the equation of the trendline and the correlation coefficient R2 on your plot. Once you have added the trendline,
- Right-click on the trendline (not on the data points). A context menu will appear.
- Select Format Trendline… A dialog box will appear.
- In the Options tab, check the Display equation on chart and Display R-squared value on chart. Excel displays R2 as the correlation coefficient.
- Click OK, and the equation and R2 values appear on the chart. These should be the same as those calculated previously.
If you followed the steps correctly, the correlation coefficient and equation should appear on the graph, as in the image below.
Before we proceed to a full discussion on Linear Regression, we will cover some basic statistics. This will give you an introduction to some of the underlying theory behind Regression Analysis.