Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0

[vladimiratanasiu]

Hello!

I posted a message (Progressive average formula that counts also blank cells), regarding a formula to calculate the progressive average for more ranges of cells (including also the blank cells when counting). I got a solution that works very fine, but only for the tables with blank cells that don't have strings of length 0. When I tried to integrate such a table in a larger workbook, I had to link its cells with other ones by ISBLANK formulas, to return blank cell if the source contains no value. In the attached file, the range I2:I53 (without totals) is the source of data, that are imported automatically in the range C2:C53. In column E, it's calculated the progressive average of all values from column C at one date, between January 2021 and the month in line with that cell. In this point I meet a big trouble, because if I have to use the ISBLANK formula. If I keep it in all cells from C2:C53 (excepting totals), the first cell that is blank by formula (C13) returns #VALUE error in E13 all next ones from column E (E14:E55). I was suggested to delete formulas from all C column blank cells, to avoid Excel consider their content strings of length 0. If I remove formula from C13, progressive average formula works without problems to the next cell blank from column C (C20). And so on. But doing in this way, the two tables are not linked completly and may generate wrong results. How could be adjusted / changed the formulas from columns E and / or C, so that the errors be removed / avoided completly?

Thank you!

2024(1).xlsx
	A	B	C		E	F	G	H	I
1	Year	Month	Monthly energy consumption (Kwh)				Source table
2	2021	Jan-21	67.82		67.82		2021	Jan-21	67.82
3		Feb-21	61.25		64.54			Feb-21	61.25
4		Mar-21	59.19		62.75			Mar-21	59.19
5		Apr-21	48.36		59.16			Apr-21	48.36
6		May-21	49.97		57.32			May-21	49.97
7		Jun-21	48.36		55.83			Jun-21	48.36
8		Jul-21	49.97		54.99			Jul-21	49.97
9		Aug-21	16.12		50.13			Aug-21	16.12
10		Sep-21	67		52.00			Sep-21	67
11		Oct-21	11		47.90			Oct-21	11
12		Nov-21	44		47.55			Nov-21	44
13		Dec-21			#VALUE!			Dec-21
14		Total	523.04					Total	523.04
15	2022	Jan-22	54		#VALUE!		2022	Jan-22	54
16		Feb-22	66		#VALUE!			Feb-22	66
17		Mar-22	78		#VALUE!			Mar-22	78
18		Apr-22	68		#VALUE!			Apr-22	68
19		May-22	-10		#VALUE!			May-22	-10
20		Jun-22			#VALUE!			Jun-22
21		Jul-22			#VALUE!			Jul-22
22		Aug-22	49		#VALUE!			Aug-22	49
23		Sep-22	27		#VALUE!			Sep-22	27
24		Oct-22	51		#VALUE!			Oct-22	51
25		Nov-22			#VALUE!			Nov-22
26		Dec-22			#VALUE!			Dec-22
27		Total	383					Total	383
28	2023	Jan-23			#VALUE!		2023	Jan-23
29		Feb-23			#VALUE!			Feb-23
30		Mar-23	106		#VALUE!			Mar-23	106
31		Apr-23	96		#VALUE!			Apr-23	96
32		May-23	46		#VALUE!			May-23	46
33		Jun-23	188		#VALUE!			Jun-23	188
34		Jul-23			#VALUE!			Jul-23
35		Aug-23	139		#VALUE!			Aug-23	139
36		Sep-23	52		#VALUE!			Sep-23	52
37		Oct-23	109		#VALUE!			Oct-23	109
38		Nov-23	41		#VALUE!			Nov-23	41
39		Dec-23	46		#VALUE!			Dec-23	46
40		Total	823					Total	823
41	2024	Jan-24	62		#VALUE!		2024	Jan-24	62
42		Feb-24	55		#VALUE!			Feb-24	55
43		Mar-24						Mar-24
44		Apr-24						Apr-24
45		May-24						May-24
46		Jun-24						Jun-24
47		Jul-24						Jul-24
48		Aug-24						Aug-24
49		Sep-24						Sep-24
50		Oct-24						Oct-24
51		Nov-24						Nov-24
52		Dec-24						Dec-24
53		Total	117					Total	117
Sheet1

Cell Formulas
Range	Formula
C41:C52,C28:C39,C15:C26,C2:C13	C2	=IF(ISBLANK(I2),"",I2)
C14,C53,C40,C27	C14	=SUM(C2:C13)
E2,E4:E53	E2	=IF(OR($B2="Total",SUMIF($B2:$B$53,"<>"&"Total",$C2:$C$53)=0),"",SUMPRODUCT($C$2:$C2*($B$2:$B2<>"Total"))/SUMPRODUCT(1*($B$2:$B2<>"Total")))
E3	E3	=IF(OR($B3="Total",SUMIF($B3:$B$53,"<>"&"Total",$C3:$C$53)=0),"",SUMPRODUCT($C$2:$C3*($B$2:$B3<>"Total"))/SUMPRODUCT(1*($B$2:$B3<>"Total")))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kvsrinivasamurthy]

I have given the formula in Post#17 of thread

Progressive average formula that counts also blank cells​

This is an ARRAY formula. In E2 copied down.
=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 {}.