Progressive average formula that counts also blank cells

vladimiratanasiu

Active Member
Joined
Dec 17, 2010
Messages
348
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello!

I posted a message some time ago (Formula to calculate automatically progressive average), getting the proper solution in that case. Basically, it calculates and shows automatically the average of all costs at some point, each time a new value is inserted and excluding the annual totals. Presently, some changes have occured, needing to adjust the old formula to the new conditions. In the attached table, the column D includes cells to show monthly energy consumption. Some cells contains non-zero values (e.g. D2:D12, D15:D19 etc.), but other ones are blank (e.g. D13, D20:D21 etc.). In column F, it's calculated the medium energy consumption, as dynamic average of all values recorded at some point. However, the formula considers only non-zero cells (see results from F13, F20:F21, F28:F29 etc.), ignoring the blank ones. I need it to be adjusted, so that can count also the blank cells, generating in column F the average values from the column J. As it's seen, the range J2:J11 has identical values with those from D2:D11, but starting with J13 there are constant differences between the cells of the two columns because of the blank cells from column D. In addition, I need the formula to take into consideration all cells after the last non-zero one (e.g. F43:F52), so that they can be blank and not show average values calculated on the basis of the according range of column D (D43:D52).

Thank you!

2024.xlsx
BCDFJ
1YearMonthMonthly energy consumption (Kwh)
22021Jan-2167.8267.8267.82
3Feb-2161.2564.5464.54
4Mar-2159.1962.7562.75
5Apr-2148.3659.1659.16
6May-2149.9757.3257.32
7Jun-2148.3655.8355.83
8Jul-2149.9754.9954.99
9Aug-2116.1250.1350.13
10Sep-216752.0052.00
11Oct-211147.9047.90
12Nov-214447.5547.55
13Dec-21 47.5543.59
14Total523.04 
152022Jan-225448.0944.39
16Feb-226649.4645.93
17Mar-227851.5048.07
18Apr-226852.6049.32
19May-22-1048.6945.83
20Jun-22 48.6943.28
21Jul-22 48.6941.00
22Aug-224948.7141.40
23Sep-222747.5040.72
24Oct-225147.6941.18
25Nov-22 47.6939.39
26Dec-22 47.6937.75
27Total383 
282023Jan-23 47.6936.24
29Feb-23 47.6934.85
30Mar-2310650.6037.48
31Apr-239652.7639.57
32May-234652.4639.79
33Jun-2318858.3544.73
34Jul-23 58.3543.29
35Aug-2313961.7146.28
36Sep-235261.3246.46
37Oct-2310963.1648.30
38Nov-234162.3348.09
39Dec-234661.7548.03
40Total823 
412024Jan-246261.7648.41
42Feb-245561.5348.58
43Mar-24 61.53blank / no value
44Apr-24 61.53blank / no value
45May-24 61.53blank / no value
46Jun-24 61.53blank / no value
47Jul-24 61.53blank / no value
48Aug-24 61.53blank / no value
49Sep-24 61.53blank / no value
50Oct-24 61.53blank / no value
51Nov-24 61.53blank / no value
52Dec-24 61.53blank / no value
53Total117
Energy consumption
Cell Formulas
RangeFormula
C2:C13C2='Costuri energie generale'!P38
C15:C26C15='Costuri energie generale'!P55
C28:C39C28='Costuri energie generale'!P74
C41:C52C41='Costuri energie generale'!P93
F2:F52F2=IF((C2="total"),"",AVERAGEIFS($D$2:D2,$C$2:C2,"<>"&"Total"))
D2:D13D2=IF(ISBLANK('Costuri energie generale'!Q38),"",'Costuri energie generale'!Q38)
D14,D53,D40,D27D14=SUM(D2:D13)
D15:D26D15=IF(ISBLANK('Costuri energie generale'!Q55),"",'Costuri energie generale'!Q55)
D28:D39D28=IF(ISBLANK('Costuri energie generale'!Q74),"",'Costuri energie generale'!Q74)
D41:D52D41=IF(ISBLANK('Costuri energie generale'!Q93),"",'Costuri energie generale'!Q93)
 
Last edited:
I think data starts from 2nd row.
In K2 copied down
Excel Formula:
=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUMPRODUCT($D$2:$D2*($C$2:$C2<>"Total"))/SUMPRODUCT(1*($C$2:$C2<>"Total")))
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
I think data starts from 2nd row.
In K2 copied down
Excel Formula:
=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUMPRODUCT($D$2:$D2*($C$2:$C2<>"Total"))/SUMPRODUCT(1*($C$2:$C2<>"Total")))
I applied it, but in the range K13:K42 it returns only errors.

2024.xlsx
BCDEKLM
1YearMonthMonthly energy consumption (Kwh)Medium monthly energy consumption (Kwh)Necessary results
22021Jan-2167.8267.8267.8267.82
3Feb-2161.2564.5464.5464.54
4Mar-2159.1962.7562.7562.75
5Apr-2148.3659.1659.1659.16
6May-2149.9757.3257.3257.32
7Jun-2148.3655.8355.8355.83
8Jul-2149.9754.9954.9954.99
9Aug-2116.1250.1350.1350.13
10Sep-216752.0052.0052.00
11Oct-211147.9047.9047.90
12Nov-214447.5547.5547.55
13Dec-2147.55#VALUE!43.59
14Total523.04  BLANK
152022Jan-225448.09#VALUE!44.39
16Feb-226649.46#VALUE!45.93
17Mar-227851.50#VALUE!48.07
18Apr-226852.60#VALUE!49.32
19May-22-1048.69#VALUE!45.83
20Jun-2248.69#VALUE!43.28
21Jul-2248.69#VALUE!41.00
22Aug-224948.71#VALUE!42.75
23Sep-222747.50#VALUE!43.14
24Oct-225147.69#VALUE!41.18
25Nov-2247.69#VALUE!39.39
26Dec-2247.69#VALUE!37.75
27Total383  BLANK
282023Jan-2347.69#VALUE!36.24
29Feb-2347.69#VALUE!34.85
30Mar-2310650.60#VALUE!37.48
31Apr-239652.76#VALUE!
32May-234652.46#VALUE!
33Jun-2318858.35#VALUE!
34Jul-2358.35#VALUE!
35Aug-2313961.71#VALUE!
36Sep-235261.32#VALUE!
37Oct-2310963.16#VALUE!
38Nov-234162.33#VALUE!
39Dec-234661.75#VALUE!
40Total823  BLANK
412024Jan-246261.76#VALUE!48.41
42Feb-245561.53#VALUE!48.58
43Mar-24 47.33, but Blank cell
44Apr-24 46.15, but Blank cell
45May-24 , but Blank cell
46Jun-24 , but Blank cell
47Jul-24 , but Blank cell
48Aug-24 , but Blank cell
49Sep-24 , but Blank cell
50Oct-24 , but Blank cell
51Nov-24 , but Blank cell
52Dec-24 , but Blank cell
53Total117 BLANK
Sheet1
Cell Formulas
RangeFormula
L2L2=D2/1
L3L3=SUM(D2:D3)/2
L4L4=SUM(D2:D4)/3
L5L5=SUM(D2:D5)/4
L6L6=SUM(D2:D6)/5
L7L7=SUM(D2:D7)/6
L8L8=SUM(D2:D8)/7
L9L9=SUM(D2:D9)/8
L10L10=SUM(D2:D10)/9
L11L11=SUM(D2:D11)/10
L12L12=SUM(D2:D12)/11
L13L13=SUM(D2:D13)/12
D14,D53,D40,D27D14=SUM(D2:D13)
L15L15=SUM(D2:D13,D15)/13
L16L16=SUM(D2:D13,D15:D16)/14
L17L17=SUM(D2:D13,D15:D17)/15
L18L18=SUM(D2:D13,D15:D18)/16
L19L19=SUM(D2:D13,D15:D19)/17
L20L20=SUM(D2:D13,D15:D20)/18
L21L21=SUM(D2:D13,D15:D21)/19
L22L22=SUM(D2:D13,D15:D23)/20
L23L23=SUM(D2:D13,D15:D24)/21
L24L24=SUM(D2:D13,D15:D25)/22
L25L25=SUM(D2:D13,D15:D26)/23
L26L26=SUM(D2:D13,D15:D26)/24
L28L28=SUM(D2:D13,D15:D26,D28)/25
L29L29=SUM(D2:D13,D15:D26,D28:D29)/26
L30L30=SUM(D2:D13,D15:D26,D28:D30)/27
E2:E42E2=IF((C2="total"),"",AVERAGEIFS($D$2:D2,$C$2:C2,"<>"&"Total"))
L41L41=SUM(D2:D13,D15:D26,D28:D39,D41)/37
L42L42=SUM(D2:D13,D15:D26,D28:D39,D41:D42)/38
L43L43=SUM(D2:D13,D15:D26,D28:D39,D41:D43)/39
L44L44=SUM(D2:D13,D15:D26,D28:D39,D41:D44)/40
K2:K53K2=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUMPRODUCT($D$2:$D2*($C$2:$C2<>"Total"))/SUMPRODUCT(1*($C$2:$C2<>"Total")))
 
Upvote 0
Upload file showing problem.
2024.xlsx
BCDEKLM
1YearMonthMonthly energy consumption (Kwh)Medium monthly energy consumption (Kwh)Necessary results
22021Jan-2167.8267.8267.8267.82
3Feb-2161.2564.5464.5464.54
4Mar-2159.1962.7562.7562.75
5Apr-2148.3659.1659.1659.16
6May-2149.9757.3257.3257.32
7Jun-2148.3655.8355.8355.83
8Jul-2149.9754.9954.9954.99
9Aug-2116.1250.1350.1350.13
10Sep-216752.0052.0052.00
11Oct-211147.9047.9047.90
12Nov-214447.5547.5547.55
13Dec-2147.55#VALUE!43.59
14Total523.04  BLANK
152022Jan-225448.09#VALUE!44.39
16Feb-226649.46#VALUE!45.93
17Mar-227851.50#VALUE!48.07
18Apr-226852.60#VALUE!49.32
19May-22-1048.69#VALUE!45.83
20Jun-2248.69#VALUE!43.28
21Jul-2248.69#VALUE!41.00
22Aug-224948.71#VALUE!42.75
23Sep-222747.50#VALUE!43.14
24Oct-225147.69#VALUE!41.18
25Nov-2247.69#VALUE!39.39
26Dec-2247.69#VALUE!37.75
27Total383  BLANK
282023Jan-2347.69#VALUE!36.24
29Feb-2347.69#VALUE!34.85
30Mar-2310650.60#VALUE!37.48
31Apr-239652.76#VALUE!
32May-234652.46#VALUE!
33Jun-2318858.35#VALUE!
34Jul-2358.35#VALUE!
35Aug-2313961.71#VALUE!
36Sep-235261.32#VALUE!
37Oct-2310963.16#VALUE!
38Nov-234162.33#VALUE!
39Dec-234661.75#VALUE!
40Total823  BLANK
412024Jan-246261.76#VALUE!48.41
42Feb-245561.53#VALUE!48.58
43Mar-24 47.33, but Blank cell
44Apr-24 46.15, but Blank cell
45May-24 , but Blank cell
46Jun-24 , but Blank cell
47Jul-24 , but Blank cell
48Aug-24 , but Blank cell
49Sep-24 , but Blank cell
50Oct-24 , but Blank cell
51Nov-24 , but Blank cell
52Dec-24 , but Blank cell
53Total117 BLANK
Sheet1
Cell Formulas
RangeFormula
L2L2=D2/1
L3L3=SUM(D2:D3)/2
L4L4=SUM(D2:D4)/3
L5L5=SUM(D2:D5)/4
L6L6=SUM(D2:D6)/5
L7L7=SUM(D2:D7)/6
L8L8=SUM(D2:D8)/7
L9L9=SUM(D2:D9)/8
L10L10=SUM(D2:D10)/9
L11L11=SUM(D2:D11)/10
L12L12=SUM(D2:D12)/11
L13L13=SUM(D2:D13)/12
D14,D53,D40,D27D14=SUM(D2:D13)
L15L15=SUM(D2:D13,D15)/13
L16L16=SUM(D2:D13,D15:D16)/14
L17L17=SUM(D2:D13,D15:D17)/15
L18L18=SUM(D2:D13,D15:D18)/16
L19L19=SUM(D2:D13,D15:D19)/17
L20L20=SUM(D2:D13,D15:D20)/18
L21L21=SUM(D2:D13,D15:D21)/19
L22L22=SUM(D2:D13,D15:D23)/20
L23L23=SUM(D2:D13,D15:D24)/21
L24L24=SUM(D2:D13,D15:D25)/22
L25L25=SUM(D2:D13,D15:D26)/23
L26L26=SUM(D2:D13,D15:D26)/24
L28L28=SUM(D2:D13,D15:D26,D28)/25
L29L29=SUM(D2:D13,D15:D26,D28:D29)/26
L30L30=SUM(D2:D13,D15:D26,D28:D30)/27
E2:E42E2=IF((C2="total"),"",AVERAGEIFS($D$2:D2,$C$2:C2,"<>"&"Total"))
L41L41=SUM(D2:D13,D15:D26,D28:D39,D41)/37
L42L42=SUM(D2:D13,D15:D26,D28:D39,D41:D42)/38
L43L43=SUM(D2:D13,D15:D26,D28:D39,D41:D43)/39
L44L44=SUM(D2:D13,D15:D26,D28:D39,D41:D44)/40
K2:K53K2=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUMPRODUCT($D$2:$D2*($C$2:$C2<>"Total"))/SUMPRODUCT(1*($C$2:$C2<>"Total")))
 
Upvote 0
The blank cells are giving problem . They strings of length 0. To confirm Go to the blank cell in column C and Just Press delete .
Any to overcome this problem I have changed the formula. Now it is ARRAY formula (needs Ctrl+Shift+Enter together).
In K2
Excel Formula:
=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUM(IFERROR($D$2:$D2*($C$2:$C2<>"Total"),0))/SUMPRODUCT(1*($C$2:$C2<>"Total")))
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
 
Upvote 0
Solution
The blank cells are giving problem . They strings of length 0. To confirm Go to the blank cell in column C and Just Press delete .
Any to overcome this problem I have changed the formula. Now it is ARRAY formula (needs Ctrl+Shift+Enter together).
In K2
Excel Formula:
=IF(OR($C2="Total",SUMIF($C2:$C$53,"<>"&"Total",$D2:$D$53)=0),"",SUM(IFERROR($D$2:$D2*($C$2:$C2<>"Total"),0))/SUMPRODUCT(1*($C$2:$C2<>"Total")))
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
I adjusted your formula and now it's working perfectly. Thank you!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,212
Members
453,023
Latest member
alabaz

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