قالب وردپرس درنا توس
Home / Tips and Tricks / How to make a linear calibration curve in Excel

How to make a linear calibration curve in Excel



  excel logo

Excel has built-in features that you can use to display your calibration data and calculate a line that suits you best. This can be helpful when writing a chemistry lab report or programming a correction factor into an equipment.

In this article, we'll look at how to use Excel to create a chart, a linear calibration curve, display the calibration curve, and then set up simple formulas using the SLOPE and INTERCEPT function to use the calibration equation in Excel.

What is a calibration curve and how is Excel useful when creating one?

To perform a calibration, compare the reading of a device (such as the temperature as a thermometer shows) to known values ​​called standards (such as freezing and boiling point in water). This allows you to create a series of data pairs that you then use to develop a calibration curve.

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

2 ° F or 100 ° ] C). When plotting the two data pairs as dots and drawing a line between them (the calibration curve), you assume that the thermometer's response is linear, you can choose any point on the line corresponding to the value the thermometer shows and you can find the corresponding "true" temperature.

Then the line essentially fills the information between the two known points so that you can reasonably be sure when estimating the actual temperature when the thermometer reads 57.2 degrees, but

Excel has functions that make it possible to plot the data heat graphically. in a chart, add a trend line (calibration curve) and show the calibration curve equation on chart. This is useful for a visual display, but you can also calculate the formula for the line using Excel's SLOPE and INTERCEPT functions. When you enter these values ​​into simple formulas, you can automatically calculate the "true" value based on any measurement.

Let's look at an example

In 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 can represent everything from the concentration of a chemical solution that we measure using a scientific instrument to the input variables in a program that controls a marble launch machine. Y values ​​will be the "responses" and they would represent the reading of the instrument provided when measuring each chemical solution or the measured distance from how far away from the starter marble landed at each input value.

After we graphically displayed the calibration curve, we use the SLOPE and INTERCEPT functions to calculate the calibration line formula and determine the concentration of an "unknown" chemical solution based on the instrument reading or determine which input we should give the program so that the marble lands a certain distance from launch.

Step One: Create Your Chart

Our simple copy worksheet consists of two columns: X value and Y value. [19659003]   create an x ​​value and y value column

Let's start by selecting data to plot in the chart.

First select the column cells "X-Value". [19659003]   select the x value column

Now press the Ctrl key and then click Y-Value column cells.

 hold Ctrl while clicking the Y value column [19659003] Go to the "Insert" tab.

 Insert the tab

Navigate to the "Chart" menu and select the first option from the "Scatter" drop-down menu.

] Select chart> scatter "width =" 314 "height =" 250 "/> </p>
<p>  A chart is shown with data points from the two columns. </p>
<p>  The chart is displayed ” width=”650″ height=”350″/> </p><div><script async src=

] Select the series by clicking on one of the blue dots.

 select the data points

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

<img class = " alignnone wp-image-399891 size-full "src =" https://www.howtogeek.com/wp-content/uploads/2018/12/xExcel-Calibration-Curve-08.png.pagespeed.gp+jp+jw + pj + ws + js + rj + rp + rw + ri + cp + md.ic.apWPBlwcal.png "alt ="

 shows the trend line now on the chart

On the right side of the screen the "Format Trendline" menu appears. Select the boxes next to "Show equation on chart" and "Show R-squared value on chart." R-squared value is a statistic that tells how close the line fits the data. The best R-squared value is 1,000, which means that each data point touches the line. As the differences between the data points and the line grow, the r-squaring value drops, with 0,000 being the lowest possible value.

 format trendline panel

The equation and R-squared statistics of the trend line will be displayed on the chart. Note that the correlation of data is very good in our example, with an R-squared value of 0.988.

The equation is in the form "Y = Mx + B" where M is the slope and B is y

 the equations now show on the chart

Now that the calibration is complete, let's work on customize the chart by editing the title and adding axis titles. [19659003] To change the table title, click on it to select the text.

 Change the chart title

Now type a new title describing the chart.

 New titles appear on the chart

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

 Go to chart tools> design "width =" 650 "height =" 225 "/> </p>
<p>  Click on the" Add a table "drop-down menu. </p>
<p>  <img class=

Now navigate to Axis Titles> Prim

 axis title is displayed

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

 ] change axis title

Now, header to axis titles> Primary vertical.

 adds a primary vertical axis title

An axis title is displayed.

 shows the new axis title

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

] redirection of the shoulder title

Your chart is now ready.

 ] shows the entire table

Step two: Calculate the line equation and the R-squared statistics

Now let's calculate the line equation and R-squared statistics using Excel's built-in SLOPE, INTERCEPT, and CORREL features.

To our sheet (in line 14) we have added titles for the three functions. We perform the actual calculations in the cells under these titles.

First, we should calculate SLOPE. Select cell A15.

 select the slope data cell

Navigate to Formulas> More Functions> Statistical> SLOPE.

 Navigate to Formulas> More Features> Statistical> SLOPE "width =" 650 "height =" 435 "/> </p><div><script async src=

The Function Arguments window pops up. In the" Known_ys "field, select or enter the Y- value

 select or type in the columns Y-Value

In the field "Known_xs", select or enter the columns X-Value columns, the order in "Known_ys" and "Known_xs" fields applies in the SLOPE function 19659003]  select or enter the columns X-Value

Click "OK." The final formula in the formula field should look like this:

= SLOPE (C3: C12, B3: B12)

Note that the value returned by the SLOPE function in cell A15 matches the value shown on the chart.

 slope value displayed [19659003] Then select cell B15 and then navigate to Formulas> More Features> Statistical> INTERCEPT.

 Navigate to Formulas> More Function is> Statistical> INTERCEPT "width =" 650 "height =" 435 "/> [19659003] The Function arguments window appears. Select or enter the Y value columns in the "Known_ys" field. </p>
<p>  <img class=

Select or enter the columns X value for the "Known_xs" field. The order in the "Known_ys" and "Known_xs" fields also plays a role in the INTERCEPT function.

<img class = "alignnone wp-image-399913 size-full" src = "https://www.howtogeek.com/wp-content/uploads/2018/12/xExcel-Calibration-Curve-30.png .pagespeed.gp + jp + jw + pj + ws + js + rj + rp + rw + ri + cp + md.ic.tSac04oGM9.png "alt =" Select or enter the columns X-Value [19659000]

Click "OK." The final formula in the Form field 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 diagram

 shows the listening function

Then select cell C15 and navigate to Formulas> More Functions> Statistical> CORREL.

 navigate to Formulas> More Features > Statistical> CORREL "width =" 650 "height =" 435 "/> </p>
<p>  The Function arguments window appears. Select or enter one of the two cell ranges for the "Array1" field. Unlike SLOPE and INTERCEPT, the order does not affect the result of the CORREL function. </p>
<p>  <img class=

Select or enter the second of e two cell ranges for the "Array2" field.

 enter the second cell range

Click "OK". The formula should look like this in the formula list:

= CORREL (B3: B12, C3: C12)

Note that the value returned by the CORREL function does not match "r-squared" – value in the chart. The CORREL function returns "R", so we need to square it to calculate "R square".

 shows the grain function

Click inside the function field and add "^ 2" to the end of the formula for the square value to be returned by the CORREL function. The supplemented formula should now look like this:

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

Press Enter.

<img class = "alignnone wp-image-399919 size-full" src = "https://www.howtogeek.com/wp-content/uploads/2018/12/xExcel-Calibration-Curve-36.png .pagespeed.gp + jp + jw + pj + ws + js + rj + rp + rw + ri + cp + md.ic.P8hezH2muO.png "alt ="

After changing the formula, "R-squared" matches value now what is shown in the diagram

 r-squaring value now matches

Step three: Set Forms for Quickly Calculate Values ​​

Now we can use these values ​​in simple formulas to determine the concentration of the "unknown" solution or which input we should enter into the code so that the marble flies a certain distance.

These steps will set up the formulas required for you to enter an X value or a Y value and get the corresponding value based on the calibration curve

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

The equation for the best fit is in for but "Y value = SLOPE * X value + INTERCEPT" so so Lving for "Y value" is done by multiplying the X value and SLOPE and then adding the values ​​INTERCEPT

 displayed based on input

As an example, we add zero as the X value. The returned Y value should be equal to the INTERCEPT of the line that best suits it. It matches, so we know the formula works correctly.

 shows the zero point when the X value is equal to INTERCEPT

Solution for the X value based on a Y value is made by subtracting the INTERCEPT from the Y value and dividing the result by the SLOPE: [Xvalue=(YvalueINTERCEPT)/SLOPE

  solve for an x ​​value based on ay value [19659003] As an example, we used the INTERCEPT as a Y value. The returned X value should be equal to zero, but the returned value is 3.14934E-06. The value returned is not zero because we accidentally truncated the INTERCEPT result when entering the value. However, the formula works correctly, since the result of the formula is 0.00000314934, which is substantially zero.

 shows a truncated result

You can specify which X value you want

 solve Y for an x ​​value

When entering something Y- value in the second thick-bound cell, you will automatically calculate the corresponding Y value. the corresponding X value. This formula is what you would use to calculate the concentration of that solution or which input needed to start the marble a certain distance.

 solve x for ay value

In this case, the instrument reads "5" so the calibration would suggest a concentration of 4.94 or we want the marble to travel five units distance so that the calibration suggests that we specify 4.94 as the input variable for the program that controls the marble player. We can be quite sure of these results because of the high R-squared value in this example.


Source link