Calculate R-squared in Excel VBA.

What is R-squared ?

The R-squared , or coefficient of determination, measures the proportion of the variance in the dependent variable that can be predicted from the independent variable(s). It ranges from 0 to 1:

• A value close to 1 indicates that the model explains a large portion of the variance.
• A value close to 0 means the model explains little of the variance.

VBA Code to Calculate R-squared

Let’s assume you have data in two columns of Excel:

• Column A: Independent variable values X
• Column B: Dependent variable values Y

We will use linear regression to calculate R-squared , which can be done using the LinEst function in VBA.

VBA Code Example to Calculate R-squared

Sub Calculate_R2()
    ' Declare variables
    Dim RangeX As Range
    Dim RangeY As Range
    Dim Results As Variant
    Dim R2 As Double
    ' Define the data ranges (A2:A10 for X, B2:B10 for Y)
    Set RangeX = Range("A2:A10")
    Set RangeY = Range("B2:B10")
    ' Use the LinEst function to perform linear regression
    ' LinEst returns an array containing several values, including R2
    Results = Application.WorksheetFunction.LinEst(RangeY, RangeX, True, True)
    ' R2 is in the third row, first column of the array returned by LinEst
    R2 = Results(3, 1)
    ' Display the R2 value in a specific cell (e.g., C1)
    Range("C1").Value = "R^2 = " & R2
End Sub

Explanation of the Code:

1. Declare variables:
   • RangeX and RangeY represent the data ranges for the independent and dependent variables, respectively.
   • Results is a variable that will hold the regression output.
   • R2 is the variable that will hold the R2R^2R2 value.
2. Define the data ranges:
   • Range(« A2:A10 ») is the range for X (independent variable), and Range(« B2:B10 ») is the range for Y (dependent variable). You can adjust these ranges based on your data.
3. Use the LinEst function:
   • Application.WorksheetFunction.LinEst(RangeY, RangeX, True, True) performs the linear regression between X and Y. This function returns an array with multiple outputs:
     • The first row contains the regression coefficients (slope, intercept).
     • The second row contains the standard errors of the coefficients.
     • The third row contains R2 (this is what we are interested in).
     • The fourth row contains the standard error of the predicted Y.
4. Accessing R2:
   • R2 is located in Results(3, 1), which corresponds to the third row, first column of the array returned by LinEst.
5. Display the result:
   • The R2 value is displayed in cell C1 along with the label « R^2 « .

How to Run the Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Click Insert and then Module to create a new module.
3. Paste the code into the module.
4. Return to Excel and press Alt + F8, select Calculate_R2, and click Run.
5. The R2R^2R2 value will be displayed in cell C1.

Example Data:

If you run the code with these data in columns A and B, the R-squared value will be displayed in cell C1.