CODEWITHSUNDEEP
vbapivot-table
Fah
Fah

Reputation: 203

Dynamic way to calculate the past 6 months Average

The excel user will export the data from an online website to excel (12 months data), so the date will be all the time different. I create a pivot table, and I have the months and total Average and Frequency. However, I need the 6 months as well, and I am not sure how to get it which time the data changes.

My question: is there any VBA code that will dynamically calculate the past 6 months Average?

Upvotes: 0

Views: 673

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

One can do this with Formula.

To get the average of the past 6 months:

=AVERAGEIFS('12 Months'!F:F,'12 Months'!A:A,">="&EDATE(TODAY(),-6),'12 Months'!A:A,"<" &TODAY()+1)

To get the frequency:

=COUNTIFS('12 Months'!A:A,">="&EDATE(TODAY(),-6),'12 Months'!A:A,"<"&TODAY()+1)

If one wants the last 12 months, change the -6 to -12 in both formula.

Upvotes: 1

Related Questions