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.

 

[sandy666]

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.

If you say so...

Have a nice day

 

[r1998]

Hello Peter
This is probably a very lengthy way of figuring out the starting date
dates are from a1 to a17

23-Sep		01-Oct
24-Sep	0
25-Sep	0
26-Sep	0
27-Sep	0
28-Sep	0
29-Sep	0
30-Sep	0
01-Oct	1
02-Oct	0
03-Oct	0
04-Oct	0
05-Oct	0
06-Oct	0
07-Oct	0
08-Oct	0
09-Oct	0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

in b2 i used this formula n dragged till b17

=IF(AND(ISBLANK(A1)=FALSE,(MONTH(A1)<>MONTH(A2))),1,0)

in c1 i used this formula

=INDEX(A:A,MATCH(1,B1:B17,0))

now value of c1 can be used as the starting date