Statistical Analysis Dashboard

' Statistical Analysis Dashboard
' Analyze data with grouping, statistics, and deviation detection

Sub PerformStatisticalAnalysis()
    ' Main analysis procedure with user input
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    ' Browse for file
    Dim filePath As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select Excel File for Analysis"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx;*.xls;*.xlsm"
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub
        filePath = .SelectedItems(1)
    End With

    ' Open workbook
    Dim analysisWB As Workbook
    Set analysisWB = Workbooks.Open(filePath, ReadOnly:=True)

    Dim ws As Worksheet
    Set ws = analysisWB.Sheets(1)

    ' Get X column (grouping column)
    Dim xCol As String
    xCol = InputBox("Enter column letter for X axis (grouping - e.g., months):", "X Column", "A")
    If xCol = "" Then Exit Sub

    ' Get Y column (values to analyse)
    Dim yCol As String
    yCol = InputBox("Enter column letter for Y axis (values - e.g., sales):", "Y Column", "B")
    If yCol = "" Then Exit Sub

    ' Get statistical operation
    Dim operation As String
    operation = InputBox("Enter statistical operation:" & vbCrLf & _
                       "SUM, COUNT, AVERAGE, MAX, MIN, STDEV", "Operation", "AVERAGE")
    operation = UCase(Trim(operation))

    ' Get deviation threshold
    Dim deviationThreshold As Double
    deviationThreshold = Val(InputBox("Enter deviation threshold (e.g., 0.1 for 10%):", "Deviation", "0.1"))

    ' Perform grouping and calculations
    Dim results As Object
    Set results = CreateObject("Scripting.Dictionary")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, xCol).End(xlUp).Row

    ' Group data
    Dim i As Long
    For i = 2 To lastRow ' Assuming row 1 is header
        Dim groupKey As String
        Dim groupValue As Double

        groupKey = CStr(ws.Range(xCol & i).Value)
        groupValue = Val(ws.Range(yCol & i).Value)

        If Not results.Exists(groupKey) Then
            ' Create new collection for this group
            Dim valueCollection As Object
            Set valueCollection = CreateObject("System.Collections.ArrayList")
            valueCollection.Add groupValue
            results.Add groupKey, valueCollection
        Else
            ' Add to existing group
            results(groupKey).Add groupValue
        End If
    Next i

    ' Calculate statistics for each group
    Dim statsResults As Object
    Set statsResults = CreateObject("Scripting.Dictionary")

' ... 185 more lines hidden ...
'
' Sign up for free to view the complete code
' Visit: vbacode.io