average only if month is over

[MDuff]

Hi I have data that is complied by month however I would like to only average the months that are over.

Fore example 8/1/2017 has not passed and I do not have full data for it so I would like it excluded from the average.

thanks a lot in advance for any help!!!


1/1/2017 13.61 %
2/1/2017 11.12 %
3/1/2017 11.49 %
4/1/2017 10.36 %
5/1/2017 10.82 %
6/1/2017 10.95 %
7/1/2017 10.31 %
8/1/2017 2.35 %

 

[Scott T]

Try

=IF(TODAY()>EOMONTH(A1,0),your average formula here,"")

 

[MrKowz]

Try:

Excel 2013/2016
	A	B	C	D
1	1/1/2017	13.61%		11.24%
2	2/1/2017	11.12%
3	3/1/2017	11.49%
4	4/1/2017	10.36%
5	5/1/2017	10.82%
6	6/1/2017	10.95%
7	7/1/2017	10.31%
8	8/1/2017	2.35%
Sheet1

Cell Formulas
Range	Formula
D1	{=AVERAGE(IF(MONTH(A1:A8)TODAY()),B1:B8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[FormR]

Hi, here is another option you could try:

Excel 2013/2016
	A	B	C	D
1	01/01/2017	13.61%		11.24%
2	02/01/2017	11.12%
3	03/01/2017	11.49%
4	04/01/2017	10.36%
5	05/01/2017	10.82%
6	06/01/2017	10.95%
7	07/01/2017	10.31%
8	08/01/2017	2.35%
Data 2 (2)

Cell Formulas
Range	Formula
D1	=AVERAGEIFS(B:B,A:A,"<" & EOMONTH(TODAY(),-1)+1)