You can use the **LinEst **method in VBA to fit a linear regression model.

This function uses the following basic syntax:

LinEst(Arg1, Arg2, Arg3, Arg4)

where:

**Arg1**: Set of y-values**Arg2**: Set of x-values**Arg3 (optional)**: TRUE = calculate intercept normally, FALSE = force intercept to be zero**Arg4 (optional)**: TRUE = calculate additional regression statistics, FALSE = only calculate coefficients

The following example shows how to use the **LinEst** method to fit a regression model in VBA in practice.

**Example: How to Use LinEst Function in VBA**

Suppose we have the following dataset that contains one predictor variable (x) and one response variable (y):

We can create the following macro that uses the **LinEst** method to fit a simple linear regression model to this dataset:

Sub UseLinEst() Range("D1:E1") = WorksheetFunction.LinEst(Range("B2:B15"), Range("A2:A15")) End Sub

When we run this macro, we receive the following output:

The values in the output represent the coefficients for the regression model:

- The coefficient for β
_{0}is**3.52169**. - The coefficient for β
_{1}is**0.693717**.

Using these values, we can write the equation for this simple regression model:

**y = 3.52169 + 0.693717(x)**

We interpret this to mean that each additional one unit increase in the predictor variable, x, is associated with an average increase of **0.693717** in the response variable, y.

We interpret the intercept term to mean that when x is equal to zero, the average value of y is **3.52169**.

If we would like to use the **LinEst** method to produce additional regression statistics, we can use the following syntax:

Sub UseLinEst() Range("D1:E5") = WorksheetFunction.LinEst(Range("B2:B15"), Range("A2:A15"),True, True) End Sub

When we run this macro, we receive the following output:

The following screenshot provides an explanation of each value in the output:

By supplying a value of **True** to the last argument of the **LinEst** method, we’re able to calculate several additional regression statistics.

**Additional Resources**

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Rank a List of Values

VBA: How to Calculate Standard Deviation of Range

VBA: How to Calculate Weighted Average

Thank you for you comprehensive explanation on Linest. How do you use the linest function if the range for x & y has empty cells?. Appreciate your help on this. Thank you.

Thiru

You have to convert blanks or empty cells to zeroes for linest to work