Breaking News
Home / Tips and Tricks / How to Perform a Linear Calibration Curve in Excel

# How to Perform a Linear Calibration Curve in Excel  Excel has built-in functions that allow you to view your calibration data and calculate a fit line. This can be useful when writing a chemistry lab report or programming a correction factor into a device.

This article describes how to use Excel to create a graph and draw a linear calibration curve. Display the formula of the calibration curve, then use the SLOPE and INTERCEPT functions to set up simple formulas to use the calibration equation in Excel.

## What is a calibration curve and how can Excel be used when creating a calibration?

How to Perform a Calibration You compare the readings of a device (such as the temperature displayed by a thermometer) with known values, known as standards (such as the freezing and boiling point of water). This allows you to create a set of data pairs that you then use to create a calibration curve.

A two-point calibration of a thermometer with the freezing and boiling points of water would have two data pairs: one of which The thermometer is placed in ice water (32 ° F or 0 ° C) and one in boiling water (21

2 ° F or 100 ° [19459007)platziert] C). If you represent these two pairs of data as points and draw a line between them (calibration curve). If you rely on the thermometer to be linear, you can choose any point on the line that matched the value you displayed and you could find the corresponding "true" temperature.

The line fills in the information between the two known points for you, so when estimating the actual temperature, when the thermometer reads 57.2 degrees, you can be pretty sure if you've never measured a "standard" which corresponds to this measured value.

Excel has features that let you graph the data pairs in a graph, add a trend line (calibration curve), and display the equation of the calibration curve in the graph. This is useful for a visual display, but you can also calculate the formula of the line using Excel's SLOPE and INTERCEPT functions. If you enter these values ​​in simple formulas, you can automatically calculate the true value based on any measurement.

## Let's look at an example

For this example, we develop a calibration curve from a series of ten data pairs, each consisting of an X value and a Y value. The X values ​​are our "standards" and can range from the concentration of a chemical solution we measure with a scientific instrument to the input variables of a program that controls a marble starter.

The values ​​are the "answers" and represent the reading of the instrument provided in the measurement of each chemical solution, or the measured distance at which distance the marble landed with each input value from the starter.

After graphing the calibration curve we use the functions SLOPE and INTERCEPT to calculate the formula of the calibration line and to determine the concentration of an "unknown" chemical solution based on the instrument's readings or decide which input we want should give the program so the ball reaches a certain distance from the launcher.

### First step: Create your diagram

Our simple example table consists of two columns: X-value and Y-value. First the data to be displayed in the graphic is selected.

First select the column cells & # 39; X-Value & # 39; out. [19659003 Now press the Ctrl key and then click on the Y value column. Go to the Insert tab. tab Navigate to the Charts menu and select the first option from the Scatter drop-down list. Select the series by clicking on one of the blue dots Selection will outline the points in Excel. Right-click on one of the points and then select the "Add trend line" option. A straight line appears in the graph. On the right side of the graph Screen becomes "Format The Trendline menu is displayed. Select the check boxes next to Show Equation in Diagram and Show R-Square Value in Diagram. The R-squared value is a statistic that tells you exactly how the line fits the data. The best R squared value is 1.000, ie each data point touches the line. As the differences between the data points and the line increase, the r-squared value decreases, with 0.000 being the lowest possible value. The equation and the R-square statistics of the trend line are displayed in the graph. Note that the correlation of the data in our example with an R-squared value of 0.988 is very good.

The equation has the form "Y = Mx + B", where M is the slope and B is the y -axis portion of the straight line. After the calibration is complete, you can now customize the table by adjusting the title and adding Axis Titles. 19659003] To change the chart title, click on it to select the text. Now enter a new title describing the chart. To add titles to the x-axis and the y-axis, first navigate to Diagram Tools> Design. Navigate to Axis Titles> Primary Horizontal An axis title is displayed. To rename the axis title, first, select the text and enter a new title. Now go to Axis Title> Primary Vertical. An axis title will appear. Rename this title by selecting the text and entering a new title.  ### Step Two: Calculate the Line Equation and R- Square Statistic

Now we compute the line equation and the R-squared statistic with the Excel-integrated SLOPE , INTERCEPT and CORREL functions.

We have added titles for these three functions to our sheet (at line 14). We do the actual calculations in the cells under these titles.

First we calculate the SLOPE. Select cell A15. . Navigate to Formulas> More Functions> Statistics> SLOPE. In the Known_xs box, select or enter the X value columns. The order of the "Known_ys" and "Known_xs" fields is important for the SLOPE function. Click "OK." The last formula in the formula bar should look like this:

` = SLO PE (C3: C12, B3: B12) `

Note that the value returned by the SLOPE function in cell A15 is matches the value shown in the graphic. Next, select cell B15 and navigate to Formulas> More Functions> Statistics> INTERCEPT. the field "Known_xs." The order of the fields & # 39; Known_ys & # 39 ;, & Known_xs & # 39; is also in of the INTERCEPT function. Click "OK." The last formula in the formula bar should look like this:

` = INTERCEPT (C3: C12, B3: B12) `

Note that the value returned by the INTERCEPT function matches the y-intercept shown in the table. Next, select cell C15 and navigate to Formulas> More Functions> Statistics> CORREL. Select the other of the two cell ranges for "Field Array2. Click" OK " The formula should look like this in the formula bar:

` = CORREL (B3: B12, C3: C12) `

Note that the value returned by the CORREL function does not match the "r The CORREL function returns "R", so we must correct it to calculate "R-squared" (squares). Click on the toolbar and insert "^ 2" into the function bar to end the formula to square the value returned by the CORREL function.The full formula should now look like this:

` = CORREL (B3: B12, C3: C12) ^ 2 `

Pressures n Enter. After changing the formula, the value for "R-squared" now matches the value shown in the table. ### . Step 3: Quickly Calculate Formulas

Now we can use these values ​​in simple formulas to determine the concentration of this "unknown" solution or what input we need to type in the code to fly the ball a certain distance.

These steps are set Select the formulas required to enter an X or Y value and get the appropriate value based on the calibration curve. The equation of the best fit line is in the form "Y value = SLOPE * X Value + INTERCEPT "The value for the" Y value "is calculated by multiplying the X value and the SLOPE value and then adding the values ​​of INTERCEPT given as an example the X value. The returned Y value should match the INTERCEPT of the best fit line. It's true, so we know that the formula works correctly. . Resolving the X value based on a Y value This is done by subtracting the INTERCEPT from the Y value and dividing the result by the SLOPE:

`  X value = (Y value INTERCEPT) / SLOPE ` As an example, we used the INTERCEPT as the y-value. The returned X value should be zero, but the returned value is 3.14934E-06. The returned value is not zero because the INTERCEPT result was accidentally truncated when the value was entered. However, the formula works correctly because the result of the formula is 0.00000314934, which is essentially zero. You can type any X value that you want in the first thick-edged cell, and Excel automatically computes the corresponding Y value. Entering a Y value in the second thick-edged cell yields the corresponding X value. With this formula, you would calculate or need the concentration of this solution to spin the sphere at a certain distance. In this case the instrument reads "5" so the calibration would suggest a concentration of 4.94 or we want the marble to cover five distance units, ie Suggest the calibration that we enter 4.94 as the input variable for the program that controls the marble thrower. We can be reasonably confident in these results because of the high R-squared value in this example.