How to write average if formulas between two month

Question

Below is Currently Using Formula to arrive averages as target for the month, In which method we need to change reference manually for all worksheets every month.

Below is basic Average formulas for Last 12 Months (May'18 - Apr'19) Which is the target for May'19.

Average Count: 380.75 Formula Used: AVERAGE(B7:B18)

Average Amount: 181111.0058 Formula Used AVERAGE(C7:C18)

I need new average formula which can take averages between May'18 to Apr'19 months based on General format the months should not be covered as date (or) Month format. It should be as General Text Value.

Answer 1

Use this for B:

=AVERAGE(INDEX(B:B,MATCH(TEXT(EOMONTH(NOW(),-1),"mmm'yy"),$A:$A,0)):INDEX(B:B,MATCH(TEXT(EOMONTH(NOW(),-12),"mmm'yy"),$A:$A,0)))

and this for C:

=AVERAGE(INDEX(B:B,MATCH(TEXT(EOMONTH(NOW(),-1),"mmm'yy"),$A:$A,0)):INDEX(B:B,MATCH(TEXT(EOMONTH(NOW(),-12),"mmm'yy"),$A:$A,0)))

It finds the correct months and sets the range.