Here is the answer for my question.
Code
Function AverageSemester()
Dim Ws As Worksheet
Dim Semester1 As Variant, Semester2 As Variant
Dim mySemester As Variant
Dim Target As Range, rngLast As Range
Dim s As String
Dim r As Integer, i As Integer
Application.Volatile
Semester1 = Array("May", "Jun", "Jul", "Aug", "Sep", "Oct")
Semester2 = Array("Nov", "Dec", "Jan", "Feb", "Mar", "Apr")
Set Ws = Sheets(1)
With Ws
r = .Range("c" & Rows.Count).End(xlUp).Row - 1
Set rngLast = .Range("b" & r).Offset(, 1)
s = .Range("b" & r)
If isSemes(s, Semester1) Then
mySemester = Semester1
Else
mySemester = Semester2
End If
For i = r To r - 12 Step -1
s = .Range("b" & i)
If isSemes(s, mySemester) Then
Set Target = .Range("b" & i).Offset(, 1)
Else
Exit For
End If
Next i
AverageSemester = WorksheetFunction.Average(.Range(Target, rngLast))
End With
End Function
Display More
And it is possible to use a excel a formula as well.
Assuming you do not have more than one year of data in the table, given the workbook you downloaded at the other website, for the Amount average for the previous six existing months:
=AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))
If you might have more than 12 months in the Pivot Table, then you need to check for the year also: