Then in column C rewrite the polynomial equation, substituting references to the values in column A for the X values.Ĭell C27= -0.000933*A27^2 + 0.04843*A27 + 0.006448 You can let Excel solve the quadratic equation for you using Goal Seek.įirst, make a copy of the original data and paste them into the spreadsheet starting at row 24. Another way would be to make the graph much larger, put in gridlines, and read the value from the graph. You can now plug your Y-value of 0.347 Absorbance into this equation and solve the resulting quadratic equation for X to get the concentration. You could do this directly from the graph, of course, but selecting things from graphs can be tricky at times. You can now see the equation and have enough significant figures so you can use the coefficients.Īnother little trick you can use to see the coefficients properly is to select the equation on the graph, drag your mouse over the equation to select it as text, then Copy it, and Paste it into a convenient cell where you can easily copy and paste individual coefficients. Click on the equation on the chart to select it, then choose Format/Selected Data Labels.įrom the window that appears, make the Font size 10 or 12 so you can see it, then click the Number tab and set Scientific Notation with three places after the decimal. (1) It is too small to see properly, and (2) the coefficients don't have enough significant figures for accurate use. The equation we get on the chart needs to be fixed up a bit before we can use it. Under Options, choose Display Equation on Chart From the window that appears, select Polynomial of Order 2. From the menu choose Chart/Add Trendline. Graph the data using a Scatter (XY) plot in the usual way. How do we do our second order fit using Excel? This tells us that doing a second order fit on these data should be professionally acceptable.
#Quadratic regression excel manual
Our results show (1.07/9.19)*100 = 12%.Ģ) We have been warned that at moderately high absorbance values we might get non-linearity when doing atomic absorption.ģ) Consulting the manual for our atomic absorption spectrometer, we find that it has built-in software for a "three-point calibration." Three points are the minimum needed to do a curved, second-order fit.
#Quadratic regression excel professional
Instead, we must rely on what a professional chemist would expect to see.ġ) We expect the error for this method to be around 1 or 2%. There is no reliable formula or computer analysis that can tell the difference between scattered data and curved data when the number of data points is small. How do we know that this is a poor choice? Do the data really have the amount of scatter shown? Shouldn't we just accept the value of 9.19 +or- 1.07 µg/mL? These questions do not have obvious answers. In the example above we see a standard LLS first order fit. A First Order Fit to the data used to construct a working curve follows the equation:įailure to check our data for linearity before performing our usual Linear Least Squares (LLS) analysis will generate a large error if the data are curved rather than straight.