Calculate the Z-score with Excel VBA

The Z-score is a statistical measure that tells you how many standard deviations a data point is from the mean of the data set. The formula to calculate the Z-score is:

Z=σX−μ​

Where:

• X is the value,
• μ is the mean of the data,
• σ is the standard deviation of the data.

Objective

We will write an Excel VBA code to calculate the Z-score for a given value in a data range.

Code Steps

1. Calculate the mean of the data.
2. Calculate the standard deviation of the data.
3. Apply the Z-score formula for each value in the given range.
4. Display the results in a specified column.

Detailed VBA Code

Here is the VBA code to calculate the Z-score for a data range in Excel:

Sub CalculateZScore()
    Dim DataRange As Range
    Dim Value As Double
    Dim Mean As Double
    Dim StdDev As Double
    Dim ZScore As Double
    Dim Cell As Range
    Dim ResultColumn As Range   
    ' Ask user to select the data range
    On Error Resume Next
    Set DataRange = Application.InputBox("Select the data range to calculate Z-score:", Type:=8)
    On Error GoTo 0   
    ' Check if the range is valid
    If DataRange Is Nothing Then
        MsgBox "No data range selected. Operation canceled.", vbExclamation
        Exit Sub
    End If   
    ' Calculate the mean and standard deviation of the selected data range
    Mean = Application.WorksheetFunction.Average(DataRange)
    StdDev = Application.WorksheetFunction.StDev(DataRange)   
    ' Check if the standard deviation is zero to avoid division by zero
    If StdDev = 0 Then
        MsgBox "Standard deviation is zero. Cannot calculate Z-scores.", vbExclamation
        Exit Sub
    End If   
    ' Ask user where to display the results
    Set ResultColumn = Application.InputBox("Select the starting cell to display Z-scores:", Type:=8)   
    ' Check if the result cell is valid
    If ResultColumn Is Nothing Then
        MsgBox "Result cell not selected. Operation canceled.", vbExclamation
        Exit Sub
    End If   
    ' Calculate the Z-score for each value in the data range and display it in the result column
    For Each Cell In DataRange
        ' Get the value of the cell
        Value = Cell.Value       
        ' Calculate the Z-score
        ZScore = (Value - Mean) / StdDev       
        ' Display the result in the corresponding result column
        ResultColumn.Offset(Cell.Row - DataRange.Row, 0).Value = ZScore
    Next Cell   
    ' Confirmation message
    MsgBox "Z-score calculation completed!", vbInformation
End Sub

Detailed Explanation of the Code

1. Ask for the Data Range:
   • The code begins by asking the user to select the data range for which they want to calculate the Z-score. This is done using the InputBox function with the Type:=8 option, which allows the user to select a range from the worksheet.
2. Calculate Mean and Standard Deviation:
   • After the user selects the data range, the code calculates the mean and standard deviation of the data using the Excel functions Average and StDev.
3. Check for Zero Standard Deviation:
   • If the standard deviation is zero (i.e., all values in the range are the same), the code displays an error message to prevent division by zero.
4. Ask for the Result Column:
   • The code asks the user to specify the starting cell in which the Z-scores will be displayed. This cell should be in an empty column.
5. Calculate Z-score for Each Value:
   • The code loops through each cell in the selected data range, calculates the Z-score using the formula Z=(X−μ)/σZ = (X – \mu) / \sigmaZ=(X−μ)/σ, and places the result in the corresponding cell of the result column.
6. Confirmation Message:
   • Once all the Z-scores are calculated, a confirmation message is displayed.

How to Use This Code

1. Open the VBA Editor:
   • Open Excel, then press Alt + F11 to open the VBA editor.
   • Click on Insert in the menu bar and select Module to insert a new module.
   • Copy and paste the code into the module.
2. Run the Code:
   • Press F5 to run the macro. The code will prompt you to select the data range and the result column.
3. Results:
   • The Z-scores will be calculated and displayed in the column you specified.

Example Use Case

If you have a data range like the following in cells A1:A5:

• A1: 12
• A2: 15
• A3: 18
• A4: 21
• A5: 24