How To Draw Standard Curve In Excel
In this guide I will explicate how to create a linear standard bend using Microsoft Excel and how to use it to calculate unknown sample values. I will employ the BCA total protein assay standards as an case.
The dataset
To create a standard curve in Microsoft Excel, two data variables are required. One set of data must exist the independent variable, which is the known values (e.g. protein standard concentrations in a BCA analysis), and the other is the dependent variable which refers to the measured values (e.g. the optical density readings of the samples). The independent information is plotted on the x-axis, whereas the dependent data is plotted on the y-axis, on a besprinkle plot.
For this case, I volition use data generated from the BCA protein assay kit to judge total protein concentrations. To create the standard curve, I have measured the absorbance of 8 standards (25, 125, 250, 500, 750, chiliad, 1500 and 2000 μg/mL total poly peptide) and a blank sample (0 μg/mL total protein) for background corrections. Hither is the data:
What we have hither is the boilerplate absorbances of each standard next to the corresponding known concentrations. In this instance, before we tin proceed nosotros need to beginning correct the data for the background dissonance.
To do this, but decrease the average absorbance values from the average absorbance value of the blank – so '0.0945'. This volition requite groundwork-corrected absorbance values. I have done this below and called the column 'Absorbance (562 nm)':
You lot know if you have done this correctly as your corrected background sample should now read '0'. See the end cavalcade in the above image.
Creating a linear standard curve in Microsoft Excel
The information is now practiced to create a standard curve in Excel. To do this information technology is easier to firstly plot a scatter plot betwixt the known values ('Protein standard') and the measured background-corrected absorbance values ('Absorbance').
- Select the two columns in Excel. To select more than than one column in Excel, hold down the 'ctrl' key while you click and drag the mouse over the data. And then go to, 'Insert > Scatter' and select the showtime ('Besprinkle') option.
2. Hopefully a besprinkle plot volition now be displayed. Nosotros next need to create a line of best fit through each of the points in the graph to create the standard bend. For this example I volition add a linear line to the graph, however, this doesn't ever accept to exist linear. To practise this, ensure the graph is selected and get to 'Design > Add Chart Element > Trendline > Linear'.
Your graph should now take a linear (directly line) fit running through it. Hopefully it passes through most of your points.
3. To understand how well the linear line fits the data, it is often useful to know what the 'R2 ' value of the line is. Simply, 'Rtwo ' refers to the coefficient of determination which represents the proportion of variance for the dependent variable that is predicted by the contained variable. R2 values range from 0 to ane, with 1 indicating a complete fit to the data and 0 beingness no goodness of fit. You may also recall of the Rtwo value as a percentage, ranging from 0% to 100%.
To display this on the graph, firstly ensure the graph is selected and get to 'Blueprint > Add together Chart Chemical element > Trendline > More Trendline Options …'. A new side-card should now appear on the right called 'Format Trendline'. Now, scroll down on this carte until you see the 'Display R-squared value on chart' choice. If you tick this, the R2 value will be added onto the graph.
You can run into that the 'Rtwo ' for the example is '0.9996'. This indicates that the measured absorbance values are highly like to the expected values (generated by the linear line). Alternatively, the results tin be interpreted as 99.96% of the variance existence explained by the linear line.
four. Additionally, the other piece of information that is required for whatever standard curve is the equation of the line. This can then be used to summate the unknown samples, based on the standard curve. And then, in the higher up example the equation can be used to work out the full protein concentrations of unknown samples.
To display the equation, tick the 'Brandish Equation on chart' pick (just in a higher place the 'Display R-squared value on nautical chart') on the 'Format Trendline' menu. In the above case, the equation of the line is 'y = 0.0009x + 0.0017'. Whereby, '0.0009' refers to the slope (gradient) of the line and '+ 0.0017' indicating the intercept of the line i.due east. the point at which information technology crosses the y axis.
Below, I will explain how to use this equation to calculate the values for the unknown samples based on the standard bend generated.
Using the line equation to calculate unknown sample values
And so you at present take the equation for the line. To determine unknown values based on this, so in this example it would be using background-corrected absorbance values for unknown samples ('y') to work out the total protein concentration ('x'), you have to re-organise the equation to decide what 'x' is. Let's practice this in the instance:
Now you tin can just use the rearranged equation to input the 'y' values in order to work out 'x'. For the example, permit'south say we take a sample that we do not know the total protein concentration. We measure the absorbance of the sample at 562 nm and subtract the background value to business relationship for the groundwork signal. The sample has a background-corrected absorbance of '0.497'. This would be our 'y' value in the equation. Entering this would requite:
So, the total protein concentration for the unknown sample is '550.33 μg/mL'.
Shortcuts for calculating R2 and the equation of the line
For those who want a rapid manner of determining the R2 and equation of the line without plotting a besprinkle plot, you can utilise the formula in Microsoft Excel to return this for you.
R2
To work out R2 using an Excel formula, enter the post-obit into a cell:
=RSQ(y values, 10 values)
Supersede the 'y values' and 'x values' with your data and hit the 'enter' key.
Equation of the line
To work out the gradient of the straight line using an Excel formula, enter the following into a cell:
=SLOPE(y values, x values)
Replace the 'y values' and 'x values' with your data and hitting the 'enter' key.
To work out the intercept of the directly line using an Excel formula, enter the following into a cell:
=INTERCEPT(y values, 10 values)
Supercede the 'y values' and 'ten values' with your data and hitting the 'enter' key.
Microsoft Excel version used: 2016
Source: https://toptipbio.com/linear-standard-curve-excel/
Posted by: terrellsuaing.blogspot.com
0 Response to "How To Draw Standard Curve In Excel"
Post a Comment