Calculate the correlation coefficient between two datasets in Excel VBA

Steps to calculate the correlation coefficient:

The correlation coefficient (often denoted as r) measures the strength and direction of the linear relationship between two variables. It ranges from -1 to 1:

• 1 means a perfect positive correlation.
• -1 means a perfect negative correlation.
• 0 means no linear correlation.

The correlation coefficient can be calculated using Excel’s CORREL function, but in VBA, we can also calculate it manually using the following formula:

r=(∑(Xi−Xˉ)(Yi−Yˉ))/sqrt(∑(Xi−Xˉ)2∑(Yi−Yˉ)2)

Where:

• x and y are the datasets.
• n is the number of data pairs.

VBA Code to calculate the correlation coefficient:

Sub CalculateCorrelation()
    ' Declare variables
    Dim RangeX As Range
    Dim RangeY As Range
    Dim i As Long
    Dim n As Long
    Dim sumX As Double
    Dim sumY As Double
    Dim sumXY As Double
    Dim sumX2 As Double
    Dim sumY2 As Double
    Dim correlation As Double
    ' Set the data ranges for X and Y
    Set RangeX = Range("A1:A10")  ' Range of X values
    Set RangeY = Range("B1:B10")  ' Range of Y values
    ' Check if the ranges have the same size
    If RangeX.Cells.Count <> RangeY.Cells.Count Then
        MsgBox "The data ranges must have the same number of cells."
        Exit Sub
    End If   
    ' Initialize the sums
    sumX = 0
    sumY = 0
    sumXY = 0
    sumX2 = 0
    sumY2 = 0
    n = RangeX.Cells.Count
    ' Calculate the necessary sums
    For i = 1 To n
        sumX = sumX + RangeX.Cells(i).Value
        sumY = sumY + RangeY.Cells(i).Value
        sumXY = sumXY + (RangeX.Cells(i).Value * RangeY.Cells(i).Value)
        sumX2 = sumX2 + (RangeX.Cells(i).Value ^ 2)
        sumY2 = sumY2 + (RangeY.Cells(i).Value ^ 2)
    Next i
    ' Calculate the correlation coefficient r
    correlation = (n * sumXY - sumX * sumY) / _
                 Sqr((n * sumX2 - sumX ^ 2) * (n * sumY2 - sumY ^ 2))
    ' Display the result in a message box
    MsgBox "The correlation coefficient is: " & correlation
End Sub

Explanation of the code:

1. Variable Declaration:
   • RangeX and RangeY represent the ranges of data in columns X and Y, respectively.
   • Variables sumX, sumY, sumXY, sumX2, and sumY2 are used to store the sums needed to calculate the correlation.
   • n is the number of data pairs.
2. Setting the data ranges:
   • The code takes the data from the ranges in columns A and B (you can adjust these ranges to suit your needs).
3. Checking the size of the ranges:
   • The code checks that both data ranges (X and Y) have the same number of cells. If they don’t, it shows a message box and stops.
4. Calculating the necessary sums:
   • A For loop goes through each pair of data points in the X and Y ranges and calculates the sums of xxx, yyy, x⋅yx \cdot yx⋅y, x2x^2×2, and y2y^2y2.
5. Calculating the correlation coefficient:
   • Using the formula for the correlation coefficient, the code computes the result.
6. Displaying the result:
   • The correlation coefficient is displayed in a message box.

How to use the code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the editor, click on Insert and then Module.
3. Paste the code into the module.
4. Close the VBA editor.
5. Go back to Excel and press Alt + F8 to run the CalculateCorrelation macro.

The result of the correlation calculation will be displayed in a message box. You can adjust the ranges Range(« A1:A10 ») and Range(« B1:B10 ») to match your actual data.