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

nguerra

New Member
Joined
Oct 1, 2013
Messages
46
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

MonthDaysMonthAvg
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​
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
=AVERAGEIF($A$2:$A$27, D2,$B$2:$B$27)
 

nguerra

New Member
Joined
Oct 1, 2013
Messages
46
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.
Code:
=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

Active Member
Joined
Mar 15, 2012
Messages
482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
this may work for you

VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
Here's some code to get the average for each month.
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,920
Messages
5,627,627
Members
416,257
Latest member
salomon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top