Work out Averages of completed Months

[drewberts]

I have a dashboard sheet which calculates the sales totals for each month (from an separate invoice sheet) based on a financial year start date. I type in the financial year start date into a cell (A1) and A2,A13 etc calculates each month for that year starting with the start date. Column B the uses SUMIFS to total the sales for each of those months (this bit all works fine).

What I want to do is work out the monthly invoice average for the completed months and for this to auto calculate each time a completed month ends. So for example I want in Cell D1 the average for the 5 completed months so far this year (Jan-May) even though Col A has all the way to Dec 2022. The current Month (June) needs to be ignored in the average formula as it is updating each time an invoice is added to the invoice sheet and therefore throws out the average calculation. I can easily ignore the future months as these are zero but I'm looking to also exclude the current month as well (but this isn't zero).

I also need the average formula to include June when we tip over into July, but then exclude July as that will have a non-zero invoice total as the month progresses.

Hope that makes sense

 

[GraH]

if you averageifs on board

Book1
	A	B	C	D	E	F	G	H
1	2022	Avrg Sales
2	31/01/2022	$ 154,40				Date	Sales
3	28/02/2022	$ 180,95				3/01/2022	$ 117,20
4	31/03/2022	$ 143,06				24/04/2022	$ 247,80
5	30/04/2022	$ 194,61				25/02/2022	$ 148,21
6	31/05/2022	$ 112,28				25/03/2022	$ 147,08
7	30/06/2022	$ 259,00				2/03/2022	$ 108,99
8	31/07/2022					12/01/2022	$ 113,66
9	31/08/2022					16/04/2022	$ 181,24
10	30/09/2022					20/02/2022	$ 181,97
11	31/10/2022					5/03/2022	$ 135,67
12	30/11/2022					11/01/2022	$ 137,82
13	31/12/2022					20/04/2022	$ 185,39
14						18/03/2022	$ 144,03
15						22/03/2022	$ 179,55
16						20/02/2022	$ 188,23
17						22/05/2022	$ 112,28
18						7/07/2022	$ 147,19
19						1/07/2022	$ 193,88
20						3/02/2022	$ 205,37
21						15/06/2022	$ 150,79
22						16/06/2022	$ 367,20
23						30/04/2022	$ 163,99
24						31/01/2022	$ 248,90
25
Sheet2

Cell Formulas
Range	Formula
A2:A13	A2	=EOMONTH(DATE(A1,SEQUENCE(12),1),0)
B2:B13	B2	=IF(TODAY()>=A2,AVERAGEIFS($G$3:$G$24,$F$3:$F$24,"<="&A2,$F$3:$F$24,">"&EOMONTH(A2,-1)),"")
Dynamic array formulas.