Mathematical Modeling: Linear Regression in Excel

14/07/26 •0•0

Here dive into the world of mathematical modeling using linear regression in Excel. Learn how to analyze data and make predictions with this powerful tool.

Écrit parLeon Diniz

Traduit parEditorial

Révisé parEditorial

What Is Mathematical Modeling?

Mathematical modeling is the process of using formulas and equations to represent real-world situations. It helps us predict results, understand relationships between variables, and make informed decisions.

Linear Regression

Regression is a statistical technique used to understand the relationship between a dependent variable (what we want to predict) and one or more independent variables (the influencing factors).

Used to predict continuous values (e.g., the price of a house based on its size). Linear Regression is a statistical technique used to model the relationship between a dependent variable (what you want to predict) and one or more independent variables (the influencing factors). The central idea is to find a straight line that best fits the data, allowing you to predict future values or understand the relationship between the variables. It tries to establish a mathematical relationship between the variables, expressed by the equation:

\[ Y = a X + b \]

Where:

Y: Dependent variable (what we want to predict).

X: Independent variable (the influencing factor).

a: Angular coefficient (slope of the line).

b: Linear coefficient (intercept, where the line crosses the Y-axis).

When to Use It?

When you want to predict a continuous value (e.g., house price, future sales). When there is an apparent linear relationship between the variables.

Linear Regression in Excel

There are 2 ways to create your linear regression in Excel. The first way uses a chart and the second uses a formula. It uses the sigmoid function to map values into probabilities:

Linear Regression Using a Chart

In Excel: Select the data. Go to the Insert tab > Scatter Chart. Right-click the points on the chart. Select Add Trendline. Choose Linear and check the option Display Equation on Chart. In the same window, check the Display R-squared value on chart box.

Chart with trendline and R² in Excel

R² (coefficient of determination) is a measure that indicates how well the linear regression model fits the data. It ranges from 0 to 1, where: R² = 1: The model explains 100% of the variability in the data. R² = 0: The model explains none of the variability in the data.

The problem with this solution is that it does not show the A and B coefficients of the Linear Regression.

Linear Regression Using a Formula

The LINEST function in Excel returns several regression statistics, including R² (some use Proj.Lin).

Linest being used in Google Sheets

=LINEST(B2:B10, A2:A10, TRUE, TRUE)

Where:

B2:B10 is the range of the dependent variable (Y).

A2:A10 is the range of the independent variable (X).

The third argument (TRUE) indicates that the intercept should be calculated.

The fourth argument (TRUE) indicates that additional statistics should be returned.

You can choose to select only Y. In this case, it will consider X as a RANGE(LEN(Y)) [1,2,3...]. And if you do not specify other arguments, the third will be considered True and the fourth False. Below is an example explicitly passing all of them: