Dynamic average of non-zero values only, or up to the present month

V

vladimiratanasiu

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

Buget de venituri si cheltuieli 2025.xlsx
AIJ
1PeriodExtra hours(%)
2
3Nov-2320.54% 
4Dec-2317.01%
5Jan-2422.50%
6Feb-2415.48%
7Mar-2419.94%
8Apr-2423.01%
9May-2425.31%
10Jun-2423.36%
11Jul-2423.10%
12Aug-2427.38%
13Sep-2418.75%
14Oct-2422.83%
15Nov-2423.21%
16Dec-2419.06%
17Jan-2542.01%
18Feb-250.00%
19Mar-250.00%
20Apr-250.00%
21May-250.00%
22Jun-250.00%
23Jul-250.00%
24Aug-250.00%
25Sep-250.00%
26Oct-250.00%
27Nov-250.00%
28Dec-250.00%13.21%
Calcul salariu
Cell Formulas
RangeFormula
J3:J28J3=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:I28I3=IF((G3-D3+H3)/D3<0,0,(G3-D3+H3)/D3)
Dynamic array formulas.
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Like this?

25 02 09.xlsm
AIJ
1PeriodExtra hours(%)
2
3Nov-2320.54%
4Dec-2317.01%
5Jan-2422.50%
6Feb-2415.48%
7Mar-2419.94%
8Apr-2423.01%
9May-2425.31%
10Jun-2423.36%
11Jul-2423.10%
12Aug-2427.38%
13Sep-2418.75%
14Oct-2422.83%
15Nov-2423.21%
16Dec-2419.06%
17Jan-2542.01%
18Feb-250.00%
19Mar-250.00%
20Apr-250.00%
21May-250.00%
22Jun-250.00%
23Jul-250.00%
24Aug-250.00%
25Sep-250.00%
26Oct-250.00%
27Nov-250.00%
28Dec-250.00%22.90%
Average
Cell Formulas
RangeFormula
J28J28=AVERAGEIFS(I3:I28,I3:I28,"<>0")
 
Upvote 0
Peter_SSs said:
Like this?

25 02 09.xlsm
AIJ
1PeriodExtra hours(%)
2
3Nov-2320.54%
4Dec-2317.01%
5Jan-2422.50%
6Feb-2415.48%
7Mar-2419.94%
8Apr-2423.01%
9May-2425.31%
10Jun-2423.36%
11Jul-2423.10%
12Aug-2427.38%
13Sep-2418.75%
14Oct-2422.83%
15Nov-2423.21%
16Dec-2419.06%
17Jan-2542.01%
18Feb-250.00%
19Mar-250.00%
20Apr-250.00%
21May-250.00%
22Jun-250.00%
23Jul-250.00%
24Aug-250.00%
25Sep-250.00%
26Oct-250.00%
27Nov-250.00%
28Dec-250.00%22.90%
Average
Cell Formulas
RangeFormula
J28J28=AVERAGEIFS(I3:I28,I3:I28,"<>0")
Click to expand...
Thank you Peter, for your prompt answer! The result of your formula is right. Anyway, I would like the average to be in line with the last non-zero value. That's why I used my formula in another table, but it didn't include zero values for next months. So, my final result was aligned with the last value. Could you adjust it in this sense? Thank you!
 
Upvote 0
vladimiratanasiu said:
I would like the average to be in line with the last non-zero value.
Click to expand...
Assuming all the zero values are together at the bottom, try ..

25 02 09.xlsm
AIJ
1PeriodExtra hours(%)
2
3Nov-2320.54% 
4Dec-2317.01% 
5Jan-2422.50% 
6Feb-2415.48% 
7Mar-2419.94% 
8Apr-2423.01% 
9May-2425.31% 
10Jun-2423.36% 
11Jul-2423.10% 
12Aug-2427.38% 
13Sep-2418.75% 
14Oct-2422.83% 
15Nov-2423.21% 
16Dec-2419.06% 
17Jan-2542.01%22.90%
18Feb-250.00% 
19Mar-250.00% 
20Apr-250.00% 
21May-250.00% 
22Jun-250.00% 
23Jul-250.00% 
24Aug-250.00% 
25Sep-250.00% 
26Oct-250.00% 
27Nov-250.00% 
28Dec-250.00% 
Average
Cell Formulas
RangeFormula
J3:J28J3=IF(AND(I3>0,I4=0),AVERAGE(I$3:I3),"")
 
Upvote 0
Array option and does not assume the 0's are together.
Book3
AIJ
1PeriodExtra hours(%)
2
311/1/2320.54% 
412/1/2317.01%
51/1/2422.50%
62/1/2415.48%
73/1/2419.94%
84/1/2423.01%
95/1/2425.31%
106/1/2423.36%
117/1/2423.10%
128/1/2427.38%
139/1/2418.75%
1410/1/2422.83%
1511/1/2423.21%
1612/1/2419.06%
171/1/2542.01%22.90%
182/1/250.00%
193/1/250.00%
204/1/250.00%
215/1/250.00%
226/1/250.00%
237/1/250.00%
248/1/250.00%
259/1/250.00%
2610/1/250.00%
2711/1/250.00%
2812/1/250.00%
Sheet3
Cell Formulas
RangeFormula
J3:J28J3=IF(MAXIFS(A3:A28,I3:I28,"<>0")=A3:A28,AVERAGEIFS(I3:I28,I3:I28,"<>0"),"")
Dynamic array formulas.
 
Upvote 0
If there might be something in I29, which would prevent J28 showing a value at the end of the year with my previous formula then perhaps this.

25 02 09.xlsm
AIJ
1PeriodExtra hours(%)
2
3Nov-2320.54% 
4Dec-2317.01% 
5Jan-2422.50% 
6Feb-2415.48% 
7Mar-2419.94% 
8Apr-2423.01% 
9May-2425.31% 
10Jun-2423.36% 
11Jul-2423.10% 
12Aug-2427.38% 
13Sep-2418.75% 
14Oct-2422.83% 
15Nov-2423.21% 
16Dec-2419.06% 
17Jan-2542.01%22.90%
18Feb-250.00% 
19Mar-250.00% 
20Apr-250.00% 
21May-250.00% 
22Jun-250.00% 
23Jul-250.00% 
24Aug-250.00% 
25Sep-250.00% 
26Oct-250.00% 
27Nov-250.00% 
28Dec-250.00% 
29???
Average (2)
Cell Formulas
RangeFormula
J3:J28J3=IF(COUNTIF(I3:I$28,">0")=1,AVERAGE(I$3:I3),"")
 
Upvote 0
Solution
Peter_SSs said:
If there might be something in I29, which would prevent J28 showing a value at the end of the year with my previous formula then perhaps this.

25 02 09.xlsm
AIJ
1PeriodExtra hours(%)
2
3Nov-2320.54% 
4Dec-2317.01% 
5Jan-2422.50% 
6Feb-2415.48% 
7Mar-2419.94% 
8Apr-2423.01% 
9May-2425.31% 
10Jun-2423.36% 
11Jul-2423.10% 
12Aug-2427.38% 
13Sep-2418.75% 
14Oct-2422.83% 
15Nov-2423.21% 
16Dec-2419.06% 
17Jan-2542.01%22.90%
18Feb-250.00% 
19Mar-250.00% 
20Apr-250.00% 
21May-250.00% 
22Jun-250.00% 
23Jul-250.00% 
24Aug-250.00% 
25Sep-250.00% 
26Oct-250.00% 
27Nov-250.00% 
28Dec-250.00% 
29???
Average (2)
Cell Formulas
RangeFormula
J3:J28J3=IF(COUNTIF(I3:I$28,">0")=1,AVERAGE(I$3:I3),"")
Click to expand...
Thank you very much, Peter! Your formula runs perfectly!

One more question: how could I calculate monthly in column E the dynamic average of work hours, starting with the first month (Nov. 2023) up to the actual one (Feb. 2025, March 2025 etc.) ? In this case, every month (A3:A28) has already a corresponding value (D3:D28), but I need the result to be calculatet and shown exactly in line with every current month (e.g. E18, for February 2025 ). Thank you!
Buget de venituri si cheltuieli 2025 (version 1).xlsb
ADE
1PeriodLegal work hours
2MonthlyMedium
3Nov-23168
4Dec-23144
5Jan-24160
6Feb-24168
7Mar-24168
8Apr-24176
9May-24160
10Jun-24152
11Jul-24184
12Aug-24168
13Sep-24168
14Oct-24184
15Nov-24168
16Dec-24160
17Jan-25144
18Feb-25160165
19Mar-25168
20Apr-25160
21May-25168
22Jun-25160
23Jul-25184
24Aug-25160
25Sep-25176
26Oct-25184
27Nov-25160
28Dec-25160
Calcul salariu
Cell Formulas
RangeFormula
E18E18=AVERAGE(D3:D18)
 
Last edited:
Upvote 0
Cubist said:
Array option and does not assume the 0's are together.
Book3
AIJ
1PeriodExtra hours(%)
2
311/1/2320.54% 
412/1/2317.01%
51/1/2422.50%
62/1/2415.48%
73/1/2419.94%
84/1/2423.01%
95/1/2425.31%
106/1/2423.36%
117/1/2423.10%
128/1/2427.38%
139/1/2418.75%
1410/1/2422.83%
1511/1/2423.21%
1612/1/2419.06%
171/1/2542.01%22.90%
182/1/250.00%
193/1/250.00%
204/1/250.00%
215/1/250.00%
226/1/250.00%
237/1/250.00%
248/1/250.00%
259/1/250.00%
2610/1/250.00%
2711/1/250.00%
2812/1/250.00%
Sheet3
Cell Formulas
RangeFormula
J3:J28J3=IF(MAXIFS(A3:A28,I3:I28,"<>0")=A3:A28,AVERAGEIFS(I3:I28,I3:I28,"<>0"),"")
Dynamic array formulas.
Click to expand...
Thank you, Cubist! Your formula is available, too!(y)
 
Upvote 0
A couple of ways, one copied down, one dynamic.

25 02 10.xlsm
ADEF
1PeriodLegal work hours
2MonthlyMedium
3Nov-23168  
4Dec-23144 
5Jan-24160 
6Feb-24168 
7Mar-24168 
8Apr-24176 
9May-24160 
10Jun-24152 
11Jul-24184 
12Aug-24168 
13Sep-24168 
14Oct-24184 
15Nov-24168 
16Dec-24160 
17Jan-25144 
18Feb-25160164.5164.5
19Mar-25168 
20Apr-25160 
21May-25168 
22Jun-25160 
23Jul-25184 
24Aug-25160 
25Sep-25176 
26Oct-25184 
27Nov-25160 
28Dec-25160 
Average
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.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,226,697
Messages
6,192,498
Members
453,727
Latest member
tuong_ng89

Share this page

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