Calculate the quartiles in Excel VBA.

Goal:

To calculate the quartiles (Q1, Q2 (median), Q3) for a given range of data. The code will take an input range of cells and return the three quartiles.

Explanation of Quartiles:

• Q1 (First Quartile): The median of the first half of the data (25% of the values).
• Q2 (Median): The median of the entire dataset (50% of the values).
• Q3 (Third Quartile): The median of the second half of the data (75% of the values).

VBA Code:

Sub CalculateQuartiles()
    ' Declare variables
    Dim Range As Range
    Dim Data() As Double
    Dim Q1 As Double
    Dim Q2 As Double
    Dim Q3 As Double
    Dim i As Integer   
    ' Prompt the user to select a range of data
    On Error Resume Next
    Set Range = Application.InputBox("Select a data range", Type:=8)
    On Error GoTo 0   
    ' Check if the range is valid
    If Range Is Nothing Then
        MsgBox "No range selected. The process is canceled.", vbCritical
        Exit Sub
    End If   
    ' Check if the selected range contains numeric values
    If WorksheetFunction.Count(Range) = 0 Then
        MsgBox "The selected range does not contain numeric values.", vbCritical
        Exit Sub
    End If   
    ' Copy the data from the range into an array
    ReDim Data(1 To Range.Cells.Count)
    For i = 1 To Range.Cells.Count
        Data(i) = Range.Cells(i).Value
    Next i   
    ' Sort the data
    Call SortArray(Data)   
    ' Calculate the quartiles
    Q1 = CalculateQuartile(Data, 0.25)
    Q2 = CalculateQuartile(Data, 0.50)
    Q3 = CalculateQuartile(Data, 0.75)   
    ' Display the results
    MsgBox "First Quartile (Q1): " & Q1 & vbCrLf & _
           "Median (Q2): " & Q2 & vbCrLf & _
           "Third Quartile (Q3): " & Q3, vbInformation   
End Sub

' Subroutine to sort the array in ascending order
Sub SortArray(ByRef Array() As Double)
    Dim i As Integer, j As Integer
    Dim Temp As Double
    For i = LBound(Array) To UBound(Array) - 1
        For j = i + 1 To UBound(Array)
            If Array(i) > Array(j) Then
                Temp = Array(i)
                Array(i) = Array(j)
                Array(j) = Temp
            End If
        Next j
    Next i
End Sub

' Function to calculate the quartile based on the percentile (p)
Function CalculateQuartile(ByRef Array() As Double, p As Double) As Double
    Dim N As Integer
    Dim Position As Double
    Dim LowerIndex As Integer
    Dim UpperIndex As Integer
    Dim LowerValue As Double
    Dim UpperValue As Double   
    N = UBound(Array) - LBound(Array) + 1
    Position = p * (N + 1)   
    ' If the position is an integer, return the value at that position
    If Position = Int(Position) Then
        CalculateQuartile = Array(Position)
    Else
        ' Otherwise, interpolate between the two adjacent values
        LowerIndex = Int(Position)
        UpperIndex = LowerIndex + 1
        LowerValue = Array(LowerIndex)
        UpperValue = Array(UpperIndex)       
        ' Linear interpolation
        CalculateQuartile = LowerValue + (Position - LowerIndex) * (UpperValue - LowerValue)
    End If
End Function

Explanation of the Code:

1. Variables and Data Range:
   • The variable Range allows the user to select a range of data in the Excel sheet.
   • If the selected range does not contain numeric data, the program displays an error message and exits.
2. Copying Data into an Array:
   • The data from the selected range is copied into an array called Data().
3. Sorting the Data:
   • The data is sorted in ascending order using the SortArray subroutine.
4. Calculating the Quartiles:
   • The CalculateQuartile function is used to calculate the quartiles Q1, Q2 (median), and Q3. The function computes the position of the quartile based on the percentile (p), and performs linear interpolation if the position is not an integer.
5. Displaying Results:
   • A message box shows the calculated quartiles.

How to Use 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. Copy and paste the VBA code into this module.
4. Close the VBA editor and return to your Excel sheet.
5. You can run the macro by pressing Alt + F8, selecting CalculateQuartiles, and clicking « Run ».

This will prompt you to select the data range, and then a message box will show the three quartiles (Q1, Q2, Q3).

Customization:

• You can extend or modify this code to calculate other statistical measures or handle more complex datasets if needed.