vladimiratanasiu
Active Member
- Joined
- Dec 17, 2010
- Messages
- 365
- Office Version
- 365
- 2021
- Platform
- Windows
Hello!
I have a recordings situation, structured as in the attached table. The column A includes months of 2025, an the column I extra hours of each month, expressed as percent. In the column J it's calculated the dynamic average (%) of extra hours. I used a formula that calculates the mentioned average, but it covers automatically all months from column A. I need it to be adjusted / a new solution to count the dynamic average, but gradually, considering only the periods with non-zero values. Or, alternatively, related to the period up to the present month.
Thank you in advance!
I have a recordings situation, structured as in the attached table. The column A includes months of 2025, an the column I extra hours of each month, expressed as percent. In the column J it's calculated the dynamic average (%) of extra hours. I used a formula that calculates the mentioned average, but it covers automatically all months from column A. I need it to be adjusted / a new solution to count the dynamic average, but gradually, considering only the periods with non-zero values. Or, alternatively, related to the period up to the present month.
Thank you in advance!
Buget de venituri si cheltuieli 2025.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | I | J | ||||||||||
1 | Period | Extra hours(%) | ||||||||||
2 | ||||||||||||
3 | Nov-23 | 20.54% | ||||||||||
4 | Dec-23 | 17.01% | ||||||||||
5 | Jan-24 | 22.50% | ||||||||||
6 | Feb-24 | 15.48% | ||||||||||
7 | Mar-24 | 19.94% | ||||||||||
8 | Apr-24 | 23.01% | ||||||||||
9 | May-24 | 25.31% | ||||||||||
10 | Jun-24 | 23.36% | ||||||||||
11 | Jul-24 | 23.10% | ||||||||||
12 | Aug-24 | 27.38% | ||||||||||
13 | Sep-24 | 18.75% | ||||||||||
14 | Oct-24 | 22.83% | ||||||||||
15 | Nov-24 | 23.21% | ||||||||||
16 | Dec-24 | 19.06% | ||||||||||
17 | Jan-25 | 42.01% | ||||||||||
18 | Feb-25 | 0.00% | ||||||||||
19 | Mar-25 | 0.00% | ||||||||||
20 | Apr-25 | 0.00% | ||||||||||
21 | May-25 | 0.00% | ||||||||||
22 | Jun-25 | 0.00% | ||||||||||
23 | Jul-25 | 0.00% | ||||||||||
24 | Aug-25 | 0.00% | ||||||||||
25 | Sep-25 | 0.00% | ||||||||||
26 | Oct-25 | 0.00% | ||||||||||
27 | Nov-25 | 0.00% | ||||||||||
28 | Dec-25 | 0.00% | 13.21% | |||||||||
Calcul salariu |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J3:J28 | J3 | =LET(a,MATCH(MAXIFS(A:A,I:I,"<>"),A:A,0)-2,b,SEQUENCE(a),c,AVERAGEIFS(OFFSET(I3,0,0,a),OFFSET(A3,0,0,a),">0"),IF(b=a,c,"")) |
I3:I28 | I3 | =IF((G3-D3+H3)/D3<0,0,(G3-D3+H3)/D3) |
Dynamic array formulas. |
Last edited: