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

#### nguerra

##### New Member
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​

### 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
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
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
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
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``````

#### nguerra

##### New Member
Thank you to everyone!

Replies
5
Views
120
Replies
21
Views
546
Replies
3
Views
134
Replies
2
Views
72
Replies
1
Views
184

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.

### Which adblocker are you using?

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

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