How do I adjust this formula to use an unknown cell?

[Peter h]

I have this formula that returns the average of data in Column D, if the date in Column A is within 2 dates. It works fine if I specify what Cell has the initial date to compare to, but as data is added, I won't always know which cell in Column A that will be. Here's the formula I have.

=ROUND(AVERAGEIFS(D:D,$A:$A,">="&$A$3,$A:$A,"<="&$A$3+(DAY(EOMONTH($A$3,0)-1))),2)

So, starting in A3 I have dates that start with "1/1/2018". So this formula gives me the average of all data that was entered in the month of January. Lets say I want to see the Average of all data that was entered in the month of February, but I don't know what cell is contains "2/1/2018". How would I change the "A3" reference to find where the next month begins? Thanks.

 

[James006]

Hello,

Do not know how you sheet is structured ...

Do you have a daily sheet starting in A3 for the whole year ?

 

[Peter h]

It's an archive file, so I'm copy and pasting rows of data every day. So Column A is a date, and Column D is the data I want the average of. So, as of right now, I have 449 rows of data, with the last row's date being 8/27/2018. But as I archive more data from my other workbook, this list will grow for the year. So I will have 12 averages at the end of the year. 1 for each month. My issue is that I don't know what row the next month will begin. I could simply go in, at the end of the year and manually put in the cell that contains the date I need for each formula, like I did with my initial formula, but I thought there has to be a way to adjust the formulas to have them written and update as data is added.

 

[sandy666]

You can try PowerQuery (2010/2013 - add-in) aka Get&Transform (2016 and higher - built-in)

in short:
- load table to PQ editor
- change date to names of month
- group by month with average

then you can add/remove any dates as you wish

 

[Peter h]

I actually figured out how I could do it. Rather than referencing a Cell address, I changed it to Date() and changed each one for each month. So for January, I used this:

=ROUND(AVERAGEIFS(D:D,$A:$A,">="&DATE(2018,1,1),$A:$A,"<="&DATE(2018,1,1)+(DAY(EOMONTH(DATE(2018,1,1),0)-1))),2)

 

[James006]

Glad you could fix your problem ...

 

[Aladin Akyurek]

I actually figured out how I could do it. Rather than referencing a Cell address, I changed it to Date() and changed each one for each month. So for January, I used this:

=ROUND(AVERAGEIFS(D:D,$A:$A,">="&DATE(2018,1,1),$A:$A,"<="&DATE(2018,1,1)+(DAY(EOMONTH(DATE(2018,1,1),0)-1))),2)

Wouldn't better to have the output like this where E:F is generated by the appropriate formulas?

Book1
	A	B	C	D	E	F	G	H
1	date			value			months	avg
2	1/31/2017			5			1/1/2017	5
3	9/23/2018			3			1/1/2018	5
4	3/18/2018			8			2/1/2018	9
5	8/10/2018			10			3/1/2018	8
6	12/24/2018			20			6/1/2018	4
7	6/1/2018			4			8/1/2018	10
8	10/20/2018			6			9/1/2018	3
9	2/17/2018			9			10/1/2018	6
10	1/19/2018			5			12/1/2018	20
11
Sheet1

If yes, what is your current date range?

 

[Peter h]

Wouldn't better to have the output like this where E:F is generated by the appropriate formulas?

Book1
	A	B	C	D	E	F	G	H
1	date			value			months	avg
2	1/31/2017			5			1/1/2017	5
3	9/23/2018			3			1/1/2018	5
4	3/18/2018			8			2/1/2018	9
5	8/10/2018			10			3/1/2018	8
6	12/24/2018			20			6/1/2018	4
7	6/1/2018			4			8/1/2018	10
8	10/20/2018			6			9/1/2018	3
9	2/17/2018			9			10/1/2018	6
10	1/19/2018			5			12/1/2018	20
11
Sheet1

If yes, what is your current date range?

I will answer your question later today, when I'm back at my computer.

 

[sandy666]

or you can simply use PivotTable, like, eg.:

Years	Date	Average of Sales
2017	Apr	28180.57​
	May	27434.71​
	Jun	29279.00​
	Jul	35538.00​
	Aug	35970.60​
	Sep	26226.50​
	Oct	28957.88​
	Nov	47713.00​
	Dec	25300.75​
2018	Jan	25462.83​
	Feb	18260.00​
	Mar	26756.50​
	Apr	37438.33​
	May	29443.86​
	Jun	33717.33​
	Jul	47539.33​
	Aug	24720.33​
	Sep	32189.50​
	Oct	23334.00​
	Nov	15397.25​
	Dec	20417.50​
2019	Jan	17381.00​
	Feb	22840.50​
	Mar	20489.40​
	Apr	34759.33​
Grand Total		27769.31​

 

[Peter h]

or you can simply use PivotTable, like, eg.:

Years	Date	Average of Sales
2017	Apr	28180.57​
	May	27434.71​
	Jun	29279.00​
	Jul	35538.00​
	Aug	35970.60​
	Sep	26226.50​
	Oct	28957.88​
	Nov	47713.00​
	Dec	25300.75​
2018	Jan	25462.83​
	Feb	18260.00​
	Mar	26756.50​
	Apr	37438.33​
	May	29443.86​
	Jun	33717.33​
	Jul	47539.33​
	Aug	24720.33​
	Sep	32189.50​
	Oct	23334.00​
	Nov	15397.25​
	Dec	20417.50​
2019	Jan	17381.00​
	Feb	22840.50​
	Mar	20489.40​
	Apr	34759.33​
Grand Total		27769.31​

I had thought about that, but I'm not as familiar with pivot tables, and they aren't quite as user friendly for other users as having formulas set up.