vladimiratanasiu
Active Member
- Joined
- Dec 17, 2010
- Messages
- 365
- Office Version
- 365
- 2021
- Platform
- 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!
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. |