Adjust formula to calculate monthly dynamic average of work hours

vladimiratanasiu

Active Member
Joined
Dec 17, 2010
Messages
365
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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
ADEFG
1PeriodLegal work hours
2MonthlyMedium
301/11/2023168  
401/12/2023144 
501/01/2024160 
601/02/2024168 
701/03/2024168 
801/04/2024176 
901/05/2024160 
1001/06/2024152 
1101/07/2024184 
1201/08/2024168 
1301/09/2024168 
1401/10/2024184 
1501/11/2024168 
1601/12/2024160 
1701/01/2025144 164.8
1801/02/2025160164.5164.5
1901/03/2025168 
2001/04/2025160 
2101/05/2025168 
2201/06/2025160 
2301/07/2025184 
2401/08/2025160 
2501/09/2025176 
2601/10/2025184 
2701/11/2025160 
2801/12/2025160 
Sheet1
Cell Formulas
RangeFormula
F3:F28F3=IF(EOMONTH(TODAY(),-1)=A3:A28-1,AVERAGEIFS(D3:D28,A3:A28,"<="&TODAY()),"")
E3:E28E3=IF(EOMONTH(TODAY(),-1)=A3-1,AVERAGE(D$3:D3),"")
Dynamic array formulas.
 
Try:
Excel Formula:
=LET(e,EOMONTH(TODAY(),-1),IF(EOMONTH(+A3:A28,0)=e,AVERAGEIFS(D3:D28,A3:A28,"<="&e),""))
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top