Dynamic way to calculate the last 6 months average.

Hello,

The excel user will export the data from an online website to excel (12 months data), so the data will be all the time different. So the formula would need to be dynamic.

Want to calculate the past 6 months average (However, the calculation need to use the months I have in the data, and sometimes there will be less than 6 or 12 months, or it may be like Jun, Aug, Sep, Dec, -2019 & Feb, Mar, Apr 2020), but I still need to get the average and frequency for it. I am trying different way with this formula

=IF(MONTH(MAX('12 Months'!A:A)-MIN('12 Months'!A:A))>=6,COUNTIF('12 Months'!A:A,">="&EDATE(MAX('12 Months'!A:A),-6))/6,AVERAGEIF('12 Months'!F:F,">="&MIN('12 Months'!F:F))/MONTH(MAX('12 Months'!F:F)-MIN('12 Months'!F:F))) ''' this well, I simple dont know.

and

=AVERAGEIF(('12 Months'!A:A),">="&EDATE(MAX('12 Months'!A:A),-12),('12 Months'!F:F)) ''' this calculates as full calendar year and not only 6 months on my data.

However it doe snot give me the correct Data.

Ps: I post a similar question on https://stackoverflow.com/ques…st-12-and-6-month-average

Here is the Answer:

A:A- Date column, B:B - Amount Column

=IF(MONTHS(MAX(A:A)-MIN(A:A))>=6;SUMIF(A:A;">="&EDATE(MAX(A:A);-6);B:B)/6;SUMIF(A:A;">="&EDATE(MAX(A:A);-6);B:B)/MONTHS(MAX(A:A)-MIN(A:A)))