VBA to find the average of one column based on the value of another column

[nguerra]

Hi everyone, I need some help in a small piece of VBA that finds the average of days for each month. Column 1 has the months and column 2 had the number of days. Sample of date as well as desired output below. Any help would be greatly appreciated.

Nick

Month	Days			Month	Avg
September	4​			September	5.5​
September	4​			October	5.88​
September	4​			November	7.44​
October	4​
November	4​
November	4​
September	4​
September	6​
November	8​
October	8​
October	8​
November	7​
September	8​
November	8​
November	8​
November	12​
October	8​
October	8​
September	8​
September	6​
November	8​
October	8​
October	3​
October	3​
October	3​
November	8​

 

[Norie]

Are you sure you need VBA?

You could do what you want with formulas or a pivot table.

Here's a formula you can use, it assumes you have the months listed in column D.

=AVERAGEIF($A$2:$A$27, D2,$B$2:$B$27)

 

[nguerra]

Are you sure you need VBA?

You could do what you want with formulas or a pivot table.

Here's a formula you can use, it assumes you have the months listed in column D.

=AVERAGEIF($A$2:$A$27, D2,$B$2:$B$27)

Norie,

Thank you. Your formula worked. This is going to be used in a larger VBA script that is doing multiple steps. I'll modify to possibly use in the VBA code. Thank you very much for your help!

 

[nemmi69]

this may work for you

Dim lRow As Long
Dim MonthLoop As Long
Dim AvgLoop As Long
Dim AvgSum As Double
Dim AvgCnt As Long
Dim MonthCount As Integer
Dim MonthName As String
Dim MonthList As Long
Dim MonthFnd As Boolean

Sub MonthAvg()
'find last row
lRow = Cells(Rows.Count, 1).End(xlUp).Row
'clear variables
MonthCount = 0
MonthName = ""
'Clear previous
Range("E2:F" & lRow).ClearContents

For MonthLoop = 2 To lRow
    If MonthCount = 0 Then
        MonthName = Range("A" & MonthLoop).Value
        Range("E2").Value = MonthName
        MonthFnd = False
    Else
        MonthFnd = False
        For MonthList = 2 To 2 + MonthCount
            If Range("A" & MonthLoop).Value = Range("E" & MonthList).Value Then
                MonthFnd = True
                Exit For
            End If
        Next MonthList
    End If
    'collect data
    If MonthFnd = False Then
        MonthName = Range("A" & MonthLoop).Value
        Range("E" & 2 + MonthCount).Value = MonthName
        AvgSum = 0
        AvgCnt = 0
        For AvgLoop = MonthLoop To lRow
            If Range("A" & AvgLoop).Value = MonthName Then
                AvgSum = AvgSum + Range("B" & AvgLoop).Value
                AvgCnt = AvgCnt + 1
            End If
        Next AvgLoop
        Range("F" & 2 + MonthCount).Value = AvgSum / AvgCnt
      
        MonthCount = MonthCount + 1
    End If
    
    
Next MonthLoop

End Sub

 

[Norie]

Here's some code to get the average for each month.

Sub GetMonthAverages()
Dim rngResults As Range
Dim dicMonths As Object
Dim arrData As Variant
Dim arrVals As Variant
Dim idxRow As Long
Dim ky As Variant

    arrData = Sheets("Sheet1").Range("A1").CurrentRegion.Value
    
    Set dicMonths = CreateObject("Scripting.Dictionary")
    
    For idxRow = 2 To UBound(arrData, 1)
        ky = arrData(idxRow, 1)
        If dicMonths.Exists(ky) Then
            arrVals = dicMonths(ky)
            arrVals(0) = arrVals(0) + 1
            arrVals(1) = arrVals(1) + arrData(idxRow, 2)
        Else
            arrVals = Array(1, arrData(idxRow, 2))
        End If
        
        dicMonths(ky) = arrVals
        
    Next idxRow
    
    Set rngResults = Sheets("Sheet1").Range("D2")
    For Each ky In dicMonths.Keys
        rngResults.Value = ky
        rngResults.Offset(, 1).Value = dicMonths(ky)(1) / dicMonths(ky)(0)
        Set rngResults = rngResults.Offset(1)
    Next ky
    
End Sub

 

[nguerra]

Thank you to everyone!