Adjust formula to calculate monthly dynamic average of work hours

[vladimiratanasiu]

Hello!

I posted a message some days ago and Peter_SSs gave me a very good solution, like in the posted table. As it's seen, both formulas from columns E and F calculate monthly dynamic average of legal work hours, up to and in line with the present month. Presently, I need a small adjustment, so that the average can be calculated and stop always in line with the previous month. In our case it should be January 2025, with the average from G17 (164.8). How could be the present formula(s) modified to meet the new conditions?

Thank you!

Book1
	A			D	E	F	G
1	Period			Legal work hours
2				Monthly	Medium
3	01/11/2023			168
4	01/12/2023			144
5	01/01/2024			160
6	01/02/2024			168
7	01/03/2024			168
8	01/04/2024			176
9	01/05/2024			160
10	01/06/2024			152
11	01/07/2024			184
12	01/08/2024			168
13	01/09/2024			168
14	01/10/2024			184
15	01/11/2024			168
16	01/12/2024			160
17	01/01/2025			144			164.8
18	01/02/2025			160	164.5	164.5
19	01/03/2025			168
20	01/04/2025			160
21	01/05/2025			168
22	01/06/2025			160
23	01/07/2025			184
24	01/08/2025			160
25	01/09/2025			176
26	01/10/2025			184
27	01/11/2025			160
28	01/12/2025			160
Sheet1

Cell Formulas
Range	Formula
F3:F28	F3	=IF(EOMONTH(TODAY(),-1)=A3:A28-1,AVERAGEIFS(D3:D28,A3:A28,"<="&TODAY()),"")
E3:E28	E3	=IF(EOMONTH(TODAY(),-1)=A3-1,AVERAGE(D$3:D3),"")
Dynamic array formulas.

 

[Cubist]

Try:

=LET(e,EOMONTH(TODAY(),-1),IF(EOMONTH(+A3:A28,0)=e,AVERAGEIFS(D3:D28,A3:A28,"<="&e),""))

 

[vladimiratanasiu]

Try:

=LET(e,EOMONTH(TODAY(),-1),IF(EOMONTH(+A3:A28,0)=e,AVERAGEIFS(D3:D28,A3:A28,"<="&e),""))

Thank you, Cubist ! Your solution is according to my needs.