Author: Alex Edwards / Source: How-To Geek
Excel has built-in features that you can use to display your calibration data and calculate a line-of-best-fit. This can be helpful when you are writing a chemistry lab report or programming a correction factor into a piece of equipment.
In this article, we’ll look at how to use Excel to create a chart, plot a linear calibration curve, display the calibration curve’s formula, and then set up simple formulas with the SLOPE and INTERCEPT functions to use the calibration equation in Excel.
What is a Calibration Curve and How is Excel Useful When Creating One?
To perform a calibration, you compare the readings of a device (like the temperature that a thermometer displays) to known values called standards (like the freezing and boiling points of water). This lets you create a series of data pairs that you’ll then use to develop a calibration curve.
A two-point calibration of a thermometer using the freezing and boiling points of water would have two data pairs: one from when the thermometer is placed in ice water (32°F or 0°C) and one in boiling water (212°F or 100°C). When you plot those two data pairs as points and draw a line between them (the calibration curve), then assuming the response of the thermometer is linear, you could pick any point on the line that corresponds to the value the thermometer displays, and you could find the corresponding “true” temperature.
So, the line is essentially filling in the information between the two known points for you so that you can be reasonably certain when estimating the actual temperature when the thermometer is reading 57.2 degrees, but when you have never measured a “standard” that corresponds to that reading.
Excel has features that allow you to plot the data pairs graphically in a chart, add a trendline (calibration curve), and display the calibration curve’s equation on the chart. This is useful for a visual display, but you can also calculate the formula of the line using Excel’s SLOPE and INTERCEPT functions. When you enter these values into simple formulas, you will be able to automatically calculate the “true” value based on any measurement.
Let’s Look at an Example
For this example, we will develop a calibration curve from a series of ten data pairs, each consisting of an X-value and a Y-value. The X-values will be our “standards,” and they could represent anything from the concentration of a chemical solution we are measuring using a scientific instrument to the input variable of a program that controls a marble launching machine.
The Y-values will be the “responses,” and they would represent the reading the instrument provided when measuring each chemical solution or the measured distance of how far away from the launcher the marble landed using each input value.
After we graphically depict the calibration curve, we will use the SLOPE and INTERCEPT functions to calculate the calibration line’s formula and determine the concentration of an “unknown” chemical solution based on the instrument’s reading or decide what input we should give the program so that the marble lands a certain distance away from the launcher.
Step One: Create Your Chart
Our simple example spreadsheet consists of two columns: X-Value and Y-Value.
Let’s start by selecting the data to plot in the chart.
First, select the ‘X-Value’ column cells.
Now press the Ctrl key and then click the Y-Value column cells.
Go to the “Insert” tab.
Navigate to the “Charts” menu and select the first option in the “Scatter” drop-down.
A chart will appear containing the data points from the two columns.
Select the series by clicking on one of the blue points. Once selected, Excel outlines the points will be outlined.
Right-click one of the points and then select the “Add Trendline” option.
A straight line will appear on the chart.
On the right side of the screen, the “Format Trendline” menu will appear. Check the boxes next to “Display Equation on chart” and “Display R-squared value on chart.” The R-squared value is a statistic that tells you how closely the line fits the data. The best R-squared value is 1.000, which means every data point touches the line. As the differences between the data points and the line grow, the r-squared value drops, with 0.000 being the lowest possible value.
The equation and R-squared statistic of the trendline will appear on the chart. Note that the correlation of the data is very good in our example, with an…
The post How to Do a Linear Calibration Curve in Excel appeared first on FeedBox.