Question

Calculate linear regression in Excel - this is how it works

Answer 1

How to do linear regression on a graph
1st step:
2nd step:
3rd step:
4th step:
5th step:
How to perform linear regression using a formula
1st step:
2nd step:
3rd step:
Quick guide: diagram
Quick guide: Formula

---

Discover interesting correlations in data with regression analysis in Excel..

Linear regression is a data plot that graphically depicts the linear relationship between an independent and a dependent variable. It is typically used to visually represent the strength of the relationship and the dispersion of the results - all for the purpose of explaining the behavior of the dependent variable. Sounds complicated, but with Microsoft Excel you can easily calculate and display linear regression.

• How to do linear regression on a graph
• How to perform linear regression using a formula

How to do linear regression on a graph

Follow our step-by-step instructions or take a look at the brief instructions .

1st step:

Select the columns in your Excel table that contain the values. Then insert a point (XY) diagram using the " Insert " tab .

2nd step:

If you wish, you can now adjust the axes. To do this, double-click on an axis and then enter the desired minimum and maximum under " Limits " .

3rd step:

If you are satisfied, click on the green plus in the top right of the diagram . Then check the "Trendline " option and select the " Linear " option using the arrow behind it . Then click on " More options ... ".

4th step:

Now check the options " Show formula in diagram " and " Show coefficient of determination in diagram ".

5th step:

You can then move the function you just inserted and the coefficient of determination to a suitable place in your diagram. You now have a linear regression graph.

How to perform linear regression using a formula

Follow our step-by-step instructions or take a look at the brief instructions ..

1st step:

First select two cells next to each other . This is important because m and b should be output afterwards.

2nd step:

Now the formula = RGP (Y_values; X_values: constants) is entered above . Enter the corresponding Y_ and X_ values ​​or ranges. You can optionally set the constant as " TRUE ". Stats can be omitted. Confirm the entry with [Ctrl] + [Shift] + [Enter] . This is important because a matrix formula is output here.

3rd step:

Excel now shows you the values ​​for m and b for the linear regression.

Quick guide: diagram

1. Select the desired value columns in Excel and then insert a point (XY) diagram using the " Insert " tab .
2. If necessary, adjust the axes by double-clicking on them and then entering the desired minimum and maximum under " Limits " .
3. Then click on the green plus at the top right of the diagram and then check the " Trend line ". Open the menu via the arrow next to "Trendline" and select the " Linear " option . Then click on " More options ... " in the same menu .
4. Now check the " Show formula in diagram " and " Show coefficient of determination in diagram ".
5. Now move the formula and the coefficient of determination to a desired position on your regression diagram.

Quick guide: Formula

1. First select two cells next to each other .
2. Enter the following formula above: " = LINEST (_values ​​y; values_x: constants) "
3. Replace_y_values ​​and_x_values ​​with your value ranges.
4. Enter " TRUE " as the constant .
5. Confirm the matrix formula with [Ctrl] + [Shift] + [Enter] .
6. Excel gives you the values ​​for m and b in the adjacent cells.

---