قالب وردپرس درنا توس
Home / Tips and Tricks / How to Perform a Linear Calibration Curve in Excel

How to Perform a Linear Calibration Curve in Excel



  Excel Logo

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. [19659003]   Creating a column with 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 Select the column with the x value

Now press the Ctrl key and then click on the Y value column.

 Hold down the Ctrl key while clicking the Y value column [19659003] Go to the Insert tab.

tab Navigate to the Charts menu and select the first option from the Scatter drop-down list.

 Choose Charts> width = "314" height = "250" src = "/pagespeed_static/1.JiBnMqyl6S.gif" onload = "pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);" onerror = "this.onerror ="; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/> </p>
<p>  A graph showing the data points of both columns appears. </p><div><script async src=

 The graph is displayed

Select the series by clicking on one of the blue dots Selection will outline the points in Excel.

 Select the data points

Right-click on one of the points and then select the "Add trend line" option.

 Select the Add Trendline option

A straight line appears in the graph.

 The trend line is now displayed 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 format trend line area

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.

 The equations are now displayed on the map

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.

 Changing the Chart Title

Now enter a new title describing the chart.

 the New titles appear in the table

To add titles to the x-axis and the y-axis, first navigate to Diagram Tools> Design.

 go to Diagram Tools> Design "width =". 650 "height =" 225 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.load IfVisibleAndMaybeBeacon (this); "/> </p>
<p>  Click the" Add Chart Item "drop-down menu. </p>
<p>  <img class=

Navigate to Axis Titles> Primary Horizontal

 Tools from head to axle> Primary horizontal

An axis title is displayed.

 The axis title appears

To rename the axis title, first, select the text and enter a new title.

 Change the Axis Title

Now go to Axis Title> Primary Vertical.

 and add a primary title for the vertical axis

An axis title will appear.

 will show the new axis title.

Rename this title by selecting the text and entering a new title.

 Renaming the axis title

Your diagram is now complete.

 Displaying the Full Diagram

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.

 Select the tilt data cell

. Navigate to Formulas> More Functions> Statistics> SLOPE.

. Navigate to Formulas> More Functions> Statistics> SLOPE "width =" 650 "height =" 435 "src =" / pagespeed_static "(this);" /> </p>
<p>  The function arguments window opens. In the Known_ys field, select or enter the Y value column. </p>
<p>  <img class=

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.

 or enter the X-Value column cells

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.

 Displayed Slope Value [19659003] Next, select cell B15 and navigate to Formulas> More Functions> Statistics> INTERCEPT.

 Navigate to Formulas> More Functions> Statistics> INTERCEPT "width =" 650 "height =" 435 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this ); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/> </p>
<p>  The" Function Arguments "window is displayed Select or enter the y-value column for the" Known_ys "field. </p>
<p>  <img class=

the field "Known_xs." The order of the fields & # 39; Known_ys & # 39 ;, & Known_xs & # 39; is also in of the INTERCEPT function.

 Select or tap in the X-Value Column Cells

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.

 with the intercept Function

Next, select cell C15 and navigate to Formulas> More Functions> Statistics> CORREL.

. Navigate to Formulas> More Functions> Statistical> CORREL. width = "650" height = "435" src = "/pagespeed_static/1.JiBnMqyl6S.gif" onload = "pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);" onerror = "this.onerror = null; pagespeed.laLLoadAccessaAr. this);" /> </p>
<p>  The "Function Arguments" window opens. Select one of the two cell ranges for "Array1" Unlike SLOPE and INTERCEPT, the order does not affect the result of the CORREL function. </p>
<p>  <img class=

Select the other of the two cell ranges for "Field Array2.

 Entering the second cell range

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

 shows the correlation function [19659003] 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.

 displays the full formula

After changing the formula, the value for "R-squared" now matches the value shown in the table.

 The R-squared value now agrees with

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

 Enter an X value or a Y value and get the corresponding value

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

 based on the input [19659003] 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.

 shows the zero value as the X value corresponding to the INTERCEPT

. 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 

 where an x-value is calculated based on a y-value [19659003] 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.

 shows a truncated result

You can type any X value that you want in the first thick-edged cell, and Excel automatically computes the corresponding Y value.

 returns Y for an x ​​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.

 x for each value

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.




Source link