Using EXCEL to do regression analysis mainly includes chart method and function method:

1, chart method:

Select two columns of data participating in linear regression (the independent variable X should be on the left side of the dependent variable Y), insert a chart, and select a scatter chart.

Select the data series in the chart, right-click, add a trend line, click the Options tab, and select Show Formula to display the r-squared value.

Note that the displayed R2 value is the square of r, so you need to use the SQRT () function to calculate the R value.

2. Functional method

If the x-value sequence is in cell A 1:A 100 and the y-value sequence is in cell B 1:B 100,

Then the intercept b of the linear formula

= intercept (B 1:B 100, A 1:A 100)

Slope k

= slope (B 1:B 100, A 1:A 100)

Correlation coefficient r

=CORREL(A 1:A 100，B 1:B 100)

or

=CORREL(B 1:B 100，A 1:A 100)

Both of the above methods can be used in regression analysis, and more satisfactory results can be obtained by combining charts and functions.