Using Excel to Do data analysis--regression analysis

Example the concentration of a solution is proportional to the peak area of the chromatographic instrument, and the standard curve of the corresponding peak area at different concentrations is to be established to test the actual concentration of unknown samples. 8 sets of corresponding data are known, the standard curve is established, the curve is evaluated and the residual data is analyzed.

This is a typical linear fitting problem, manual calculation is to use the least square method to find the fitting line of the undetermined parameters, at the same time can get the value of R, that is, the size of the correlation coefficient. In Excel, you can complete the first two steps by drawing and then adding a trendline.

Select pairs of data columns and use them as "X, y scatter graphs" to scatter the plot.

Right-click on the data point, select "Add Trendline"-"linear", and ask for formulas and correlation coefficients in the Options tab to get the fitted straight line.

As we know from the diagram, the fitted line is Y=15620X+6606.1,R2 with a value of 0.9994.

Because R2 >0.99, so this is a very obvious experimental model of linear characteristics, that is, the fitting line can be explained by more than 99.99%, covering the measured data, has a good general, can be used as a standard work curve for other unknown concentration solution measurement.

To further use more metrics to describe this model, we use the "regression" tool in data analysis to analyze this set of data in detail.

There is a lot more detail in the tabs, and note that you select the data columns for x and Y. "Constant Zero" is to indicate that the model is strictly proportional to the model, this example is true, because at the concentration of zero the corresponding peak area must be zero. The previous regression equation, although the fitting degree is quite high, still has the corresponding numerical value when x=0, which is obviously a ridiculous conclusion. So we choose "constant zero".

The "Regression" tool provides us with three graphs, namely residual plot, linear fitted graphs and normal probability graphs. The emphasis is on residual graphs and linear fitting graphs.

In the linear fitting graph, we can see that not only the data points generated according to the requirement, but also the predicted data points which are processed and fitted, the parameters of the fitting line are displayed in detail in the data table. This example aims to provide more information to play a useful role, due to the plethora of professional terminology, please readers in accordance with the actual use of the specific reference to the parameters, this no longer to further explain the details.

Residual graph is a chart of the gap between the century and the predicted value, if the residual map scattered in the upper and lower sides of the Zhongzhou distribution, then fitted straight line is reasonable, otherwise it needs to be dealt with again.

More information in the generated table, detailed parametric items can fully meet the requirements of regression analysis. The following figure provides a regression analysis of a fitting straight line to the difference between the poor and the standard bad information.