Adjust formula of dynamic average

[vladimiratanasiu]

Hello!

I posted some weeks ago a message regarding a dynamic formula of monthly costs, getting in the answer Formula to calculate dynamic average of monthly costs a good solution adapted to the conditions at that time. Shortely, it calculates dinamically (see value from D88) the average of all monthly values , excepting the annually totals and moving down in line with the each new number inserted. If I add the new values manually, the formula works according to my needs. The problem is when my monthly cells are linked by formulas with data from another table (see F51:F88) and the values are imported automatically from it. In our example, the cells from C85:C88 are linked with those from F85:F88, but the last ones have no content yet. In these conditions, the cells C85:C88 show value 0 / blank space, and the average (D88) is calculated and moves automatically in line with the last cell (C88). My question is how could be adjusted the given formula, so that the right result (e.g. value from E84) can be shown progressively in line only with the last cell (e.g. C84) related with real data from the source table , and can avoid in calculation the next ones (C85:C88) that have no equivalent numerical in the source range.

Thank you!

Book1.xlsx
	A	B	C	D	E	F
1	Year	Month	Costs
2	2017	Apr-17	34.83			34.83
3		May-17	72			72
4		Jun-17	74.77			74.77
5		Jul-17	77.74			77.74
6		Aug-17	76.5			76.5
7		Sep-17	67.74			67.74
8		Oct-17	130.11			130.11
9		Nov-17	183.44			183.44
10		Dec-17	189.56			189.56
11		Total	906.69
12	2018	Jan-18	189.56			189.56
13		Feb-18	169.62			169.62
14		Mar-18	177.41			177.41
15		Apr-18	128.08			128.08
16		May-18	132.35			132.35
17		Jun-18	128.08			128.08
18		Jul-18	132.35			132.35
19		Aug-18	132.35			132.35
20		Sep-18	113.2			113.2
21		Oct-18	78.29			78.29
22		Nov-18	75.77			75.77
23		Dec-18	78.29			78.29
24		Total	1535.35
25	2019	Jan-19	78.29			78.29
26		Feb-19	70.72			70.72
27		Mar-19	71.48			71.48
28		Apr-19	64.42			64.42
29		May-19	66.57			66.57
30		Jun-19	64.42			64.42
31		Jul-19	66.57			66.57
32		Aug-19	66.57			66.57
33		Sep-19	66.29			66.29
34		Oct-19	69.79			69.79
35		Nov-19	67.54			67.54
36		Dec-19	69.79			69.79
37		Total	822.45
38	2020	Jan-20	69.79			69.79
39		Feb-20	65.29			65.29
40		Mar-20	56.51			56.51
41		Apr-20	54.27			54.27
42		May-20	56.08			56.08
43		Jun-20	54.27			54.27
44		Jul-20	56.08			56.08
45		Aug-20	56.08			56.08
46		Sep-20	55.07			55.07
47		Oct-20	57.84			57.84
48		Nov-20	55.97			55.97
49		Dec-20	13.06			13.06
50		Total	650.31
51	2021	1/1/2021	67.82			67.82
52		2/1/2021	61.25			61.25
53		3/1/2021	59.19			59.19
54		4/1/2021	48.36			48.36
55		5/1/2021	49.97			49.97
56		6/1/2021	48.36			48.36
57		7/1/2021	49.97			49.97
58		8/1/2021	16.12			16.12
59		9/1/2021	67			67
60		10/1/2021	11			11
61		11/1/2021	44			44
62		12/1/2021	0			0
63		Total	523.04
64	2022	1/1/2022	54			54
65		2/1/2022	66			66
66		3/1/2022	78			78
67		4/1/2022	68			68
68		5/1/2022	-10			-10
69		6/1/2022	0			0
70		7/1/2022	0			0
71		8/1/2022	49			49
72		9/1/2022	27			27
73		10/1/2022	51			51
74		11/1/2022	0			0
75		12/1/2022	0			0
76		Total	383
77	2023	1/1/2023	0			0
78		2/1/2023	0			0
79		3/1/2023	106			106
80		4/1/2023	96			96
81		5/1/2023	46			46
82		6/1/2023	188			188
83		7/1/2023	0			0
84		8/1/2023	139		70.07584	139
85		9/1/2023	0
86		10/1/2023	0
87		11/1/2023	0
88		12/1/2023	0	66.61531
89		Total	575
Sheet1

Cell Formulas
Range	Formula
D2:D88	D2	=LET(a,COUNTA(C:C)-2,bb,SEQUENCE(a),c,AVERAGEIFS(C:C,B:B,">1"),IF(bb=a,c,""))
C2:C10,C12:C23,C25:C36,C38:C49,C51:C62,C64:C75,C77:C88	C2	=F2
C11	C11	=SUM(C2:C10)
C24,C37,C50,C63,C76,C89	C24	=SUM(C12:C23)
Dynamic array formulas.

 

[JamesCanale]

MrExcelPlayground19.xlsx
	A	B	C	D	E	F
1	Year	Month	Costs
2	2017	42826	34.83			34.83
3		42856	72			72
4		42887	74.77			74.77
5		42917	77.74			77.74
6		42948	76.5			76.5
7		42979	67.74			67.74
8		43009	130.11			130.11
9		43040	183.44			183.44
10		43070	189.56			189.56
11		Total	906.69
12	2018	43101	189.56			189.56
13		43132	169.62			169.62
14		43160	177.41			177.41
15		43191	128.08			128.08
16		43221	132.35			132.35
17		43252	128.08			128.08
18		43282	132.35			132.35
19		43313	132.35			132.35
20		43344	113.2			113.2
21		43374	78.29			78.29
22		43405	75.77			75.77
23		43435	78.29			78.29
24		Total	1535.35
25	2019	43466	78.29			78.29
26		43497	70.72			70.72
27		43525	71.48			71.48
28		43556	64.42			64.42
29		43586	66.57			66.57
30		43617	64.42			64.42
31		43647	66.57			66.57
32		43678	66.57			66.57
33		43709	66.29			66.29
34		43739	69.79			69.79
35		43770	67.54			67.54
36		43800	69.79			69.79
37		Total	822.45
38	2020	43831	69.79			69.79
39		43862	65.29			65.29
40		43891	56.51			56.51
41		43922	54.27			54.27
42		43952	56.08			56.08
43		43983	54.27			54.27
44		44013	56.08			56.08
45		44044	56.08			56.08
46		44075	55.07			55.07
47		44105	57.84			57.84
48		44136	55.97			55.97
49		44166	13.06			13.06
50		Total	650.31
51	2021	1/1/2021	67.82			67.82
52		2/1/2021	61.25			61.25
53		3/1/2021	59.19			59.19
54		4/1/2021	48.36			48.36
55		5/1/2021	49.97			49.97
56		6/1/2021	48.36			48.36
57		7/1/2021	49.97			49.97
58		8/1/2021	16.12			16.12
59		9/1/2021	67			67
60		10/1/2021	11			11
61		11/1/2021	44			44
62		12/1/2021	0			0
63		Total	523.04
64	2022	1/1/2022	54			54
65		2/1/2022	66			66
66		3/1/2022	78			78
67		4/1/2022	68			68
68		5/1/2022	-10			-10
69		6/1/2022	0			0
70		7/1/2022	0			0
71		8/1/2022	49			49
72		9/1/2022	27			27
73		10/1/2022	51			51
74		11/1/2022	0			0
75		12/1/2022	0			0
76		Total	383
77	2023	1/1/2023	0			0
78		2/1/2023	0			0
79		3/1/2023	106			106
80		4/1/2023	96			96
81		5/1/2023	46			46
82		6/1/2023	188			188
83		7/1/2023	0			0
84		8/1/2023	139	70.07584	70.07584	139
85		9/1/2023	0
86		10/1/2023	0
87		11/1/2023	0
88		12/1/2023	0
89		Total	575
Sheet34

Cell Formulas
Range	Formula
D2:D84	D2	=LET(a,MAX(--(F:F<>"")*ROW(F:F))-1,bb,SEQUENCE(a),c,AVERAGE(F:F),IF(bb=a,c,""))
C2:C10,C12:C23,C25:C36,C38:C49,C51:C62,C64:C75,C77:C88	C2	=F2
C11	C11	=SUM(C2:C10)
C24,C37,C50,C63,C76,C89	C24	=SUM(C12:C23)
Dynamic array formulas.

 

[Dave Patton]

You can use a built in feature of Excel; check information on Data Subtotal
Select your range of Data select the feature Subtotal
- at each change of Year Average

The Data Subtotal will provide 3 choices all detail, by month , and grand total
The post shows by month

N.B, The Data Subtotal enters all the formulas.

T202308a.xlsm
	A	B	C
1	Year	Month	Costs
11	2017		100.74
24	2018		127.95
37	2019		68.54
50	2020		54.19
63	2021		43.59
76	2022		31.92
85	2023		71.88
86	Grand Average		70.08
87
4ee

Cell Formulas
Range	Formula
C11	C11	=SUBTOTAL(1,C2:C10)
C24,C76,C63,C50,C37	C24	=SUBTOTAL(1,C12:C23)
C85	C85	=SUBTOTAL(1,C77:C84)
C86	C86	=SUBTOTAL(1,C2:C84)

 

[vladimiratanasiu]

MrExcelPlayground19.xlsx
	A	B	C	D	E	F
1	Year	Month	Costs
2	2017	42826	34.83			34.83
3		42856	72			72
4		42887	74.77			74.77
5		42917	77.74			77.74
6		42948	76.5			76.5
7		42979	67.74			67.74
8		43009	130.11			130.11
9		43040	183.44			183.44
10		43070	189.56			189.56
11		Total	906.69
12	2018	43101	189.56			189.56
13		43132	169.62			169.62
14		43160	177.41			177.41
15		43191	128.08			128.08
16		43221	132.35			132.35
17		43252	128.08			128.08
18		43282	132.35			132.35
19		43313	132.35			132.35
20		43344	113.2			113.2
21		43374	78.29			78.29
22		43405	75.77			75.77
23		43435	78.29			78.29
24		Total	1535.35
25	2019	43466	78.29			78.29
26		43497	70.72			70.72
27		43525	71.48			71.48
28		43556	64.42			64.42
29		43586	66.57			66.57
30		43617	64.42			64.42
31		43647	66.57			66.57
32		43678	66.57			66.57
33		43709	66.29			66.29
34		43739	69.79			69.79
35		43770	67.54			67.54
36		43800	69.79			69.79
37		Total	822.45
38	2020	43831	69.79			69.79
39		43862	65.29			65.29
40		43891	56.51			56.51
41		43922	54.27			54.27
42		43952	56.08			56.08
43		43983	54.27			54.27
44		44013	56.08			56.08
45		44044	56.08			56.08
46		44075	55.07			55.07
47		44105	57.84			57.84
48		44136	55.97			55.97
49		44166	13.06			13.06
50		Total	650.31
51	2021	1/1/2021	67.82			67.82
52		2/1/2021	61.25			61.25
53		3/1/2021	59.19			59.19
54		4/1/2021	48.36			48.36
55		5/1/2021	49.97			49.97
56		6/1/2021	48.36			48.36
57		7/1/2021	49.97			49.97
58		8/1/2021	16.12			16.12
59		9/1/2021	67			67
60		10/1/2021	11			11
61		11/1/2021	44			44
62		12/1/2021	0			0
63		Total	523.04
64	2022	1/1/2022	54			54
65		2/1/2022	66			66
66		3/1/2022	78			78
67		4/1/2022	68			68
68		5/1/2022	-10			-10
69		6/1/2022	0			0
70		7/1/2022	0			0
71		8/1/2022	49			49
72		9/1/2022	27			27
73		10/1/2022	51			51
74		11/1/2022	0			0
75		12/1/2022	0			0
76		Total	383
77	2023	1/1/2023	0			0
78		2/1/2023	0			0
79		3/1/2023	106			106
80		4/1/2023	96			96
81		5/1/2023	46			46
82		6/1/2023	188			188
83		7/1/2023	0			0
84		8/1/2023	139	70.07584	70.07584	139
85		9/1/2023	0
86		10/1/2023	0
87		11/1/2023	0
88		12/1/2023	0
89		Total	575
Sheet34

Cell Formulas
Range	Formula
D2:D84	D2	=LET(a,MAX(--(F:F<>"")*ROW(F:F))-1,bb,SEQUENCE(a),c,AVERAGE(F:F),IF(bb=a,c,""))
C2:C10,C12:C23,C25:C36,C38:C49,C51:C62,C64:C75,C77:C88	C2	=F2
C11	C11	=SUM(C2:C10)
C24,C37,C50,C63,C76,C89	C24	=SUM(C12:C23)
Dynamic array formulas.

Hello!

I posted some weeks ago a message regarding a dynamic formula of monthly costs, getting in the answer Formula to calculate dynamic average of monthly costs a good solution adapted to the conditions at that time. Shortely, it calculates dinamically (see value from D88) the average of all monthly values , excepting the annually totals and moving down in line with the each new number inserted. If I add the new values manually, the formula works according to my needs. The problem is when my monthly cells are linked by formulas with data from another table (see F51:F88) and the values are imported automatically from it. In our example, the cells from C85:C88 are linked with those from F85:F88, but the last ones have no content yet. In these conditions, the cells C85:C88 show value 0 / blank space, and the average (D88) is calculated and moves automatically in line with the last cell (C88). My question is how could be adjusted the given formula, so that the right result (e.g. value from E84) can be shown progressively in line only with the last cell (e.g. C84) related with real data from the source table , and can avoid in calculation the next ones (C85:C88) that have no equivalent numerical in the source range.

Thank you!

Book1.xlsx
	A	B	C	D	E	F
1	Year	Month	Costs
2	2017	Apr-17	34.83			34.83
3		May-17	72			72
4		Jun-17	74.77			74.77
5		Jul-17	77.74			77.74
6		Aug-17	76.5			76.5
7		Sep-17	67.74			67.74
8		Oct-17	130.11			130.11
9		Nov-17	183.44			183.44
10		Dec-17	189.56			189.56
11		Total	906.69
12	2018	Jan-18	189.56			189.56
13		Feb-18	169.62			169.62
14		Mar-18	177.41			177.41
15		Apr-18	128.08			128.08
16		May-18	132.35			132.35
17		Jun-18	128.08			128.08
18		Jul-18	132.35			132.35
19		Aug-18	132.35			132.35
20		Sep-18	113.2			113.2
21		Oct-18	78.29			78.29
22		Nov-18	75.77			75.77
23		Dec-18	78.29			78.29
24		Total	1535.35
25	2019	Jan-19	78.29			78.29
26		Feb-19	70.72			70.72
27		Mar-19	71.48			71.48
28		Apr-19	64.42			64.42
29		May-19	66.57			66.57
30		Jun-19	64.42			64.42
31		Jul-19	66.57			66.57
32		Aug-19	66.57			66.57
33		Sep-19	66.29			66.29
34		Oct-19	69.79			69.79
35		Nov-19	67.54			67.54
36		Dec-19	69.79			69.79
37		Total	822.45
38	2020	Jan-20	69.79			69.79
39		Feb-20	65.29			65.29
40		Mar-20	56.51			56.51
41		Apr-20	54.27			54.27
42		May-20	56.08			56.08
43		Jun-20	54.27			54.27
44		Jul-20	56.08			56.08
45		Aug-20	56.08			56.08
46		Sep-20	55.07			55.07
47		Oct-20	57.84			57.84
48		Nov-20	55.97			55.97
49		Dec-20	13.06			13.06
50		Total	650.31
51	2021	1/1/2021	67.82			67.82
52		2/1/2021	61.25			61.25
53		3/1/2021	59.19			59.19
54		4/1/2021	48.36			48.36
55		5/1/2021	49.97			49.97
56		6/1/2021	48.36			48.36
57		7/1/2021	49.97			49.97
58		8/1/2021	16.12			16.12
59		9/1/2021	67			67
60		10/1/2021	11			11
61		11/1/2021	44			44
62		12/1/2021	0			0
63		Total	523.04
64	2022	1/1/2022	54			54
65		2/1/2022	66			66
66		3/1/2022	78			78
67		4/1/2022	68			68
68		5/1/2022	-10			-10
69		6/1/2022	0			0
70		7/1/2022	0			0
71		8/1/2022	49			49
72		9/1/2022	27			27
73		10/1/2022	51			51
74		11/1/2022	0			0
75		12/1/2022	0			0
76		Total	383
77	2023	1/1/2023	0			0
78		2/1/2023	0			0
79		3/1/2023	106			106
80		4/1/2023	96			96
81		5/1/2023	46			46
82		6/1/2023	188			188
83		7/1/2023	0			0
84		8/1/2023	139		70.07584	139
85		9/1/2023	0
86		10/1/2023	0
87		11/1/2023	0
88		12/1/2023	0	66.61531
89		Total	575
Sheet1

Cell Formulas
Range	Formula
D2:D88	D2	=LET(a,COUNTA(C:C)-2,bb,SEQUENCE(a),c,AVERAGEIFS(C:C,B:B,">1"),IF(bb=a,c,""))
C2:C10,C12:C23,C25:C36,C38:C49,C51:C62,C64:C75,C77:C88	C2	=F2
C11	C11	=SUM(C2:C10)
C24,C37,C50,C63,C76,C89	C24	=SUM(C12:C23)
Dynamic array formulas.

Thank you for the answers! In my previous message, I mentioned an external table as reference source for the imported data, placing it as example in column F of the same sheet. However, the real ground is a bit different. The below table is integrated in the attachment Energy.xlsx, that includes the workbook with real data configuration. Sheet2 has a table, with the costs information in column C. The range C2:C24 includes non-linked information, about years 2017-2018. For years 2019-2023 (C25:C89), the monthly costs are imported automatically by formulas, from the range Q4:Q85 of Sheet1. I kept in the Sheet1 also the real formatted structure of the source table. Considering this new context, I meet a great issue in adjusting your last formula, so that it can integrate and process data from the two different sheets. Could you help me to solve this problem? Thank you!

Energy.xlsx
	A	B	C
1	Year	Month	Costs
2	2017	Apr-17	34.83
3		May-17	72
4		Jun-17	74.77
5		Jul-17	77.74
6		Aug-17	76.5
7		Sep-17	67.74
8		Oct-17	130.11
9		Nov-17	183.44
10		Dec-17	189.56
11		Total	906.69
12	2018	Jan-18	189.56
13		Feb-18	169.62
14		Mar-18	177.41
15		Apr-18	128.08
16		May-18	132.35
17		Jun-18	128.08
18		Jul-18	132.35
19		Aug-18	132.35
20		Sep-18	113.2
21		Oct-18	78.29
22		Nov-18	75.77
23		Dec-18	78.29
24		Total	1535.35
25	2019	Jan-19	78.29
26		Feb-19	70.72
27		Mar-19	71.48
28		Apr-19	64.42
29		May-19	66.57
30		Jun-19	64.42
31		Jul-19	66.57
32		Aug-19	66.57
33		Sep-19	66.29
34		Oct-19	69.79
35		Nov-19	67.54
36		Dec-19	69.79
37		Total	822.45
38	2020	Jan-20	69.79
39		Feb-20	65.29
40		Mar-20	56.51
41		Apr-20	54.27
42		May-20	56.08
43		Jun-20	54.27
44		Jul-20	56.08
45		Aug-20	56.08
46		Sep-20	55.07
47		Oct-20	57.84
48		Nov-20	55.97
49		Dec-20	13.06
50		Total	650.31
51	2021	Jan-21	67.82
52		Feb-21	61.25
53		Mar-21	59.19
54		Apr-21	48.36
55		May-21	49.97
56		Jun-21	48.36
57		Jul-21	49.97
58		Aug-21	16.12
59		Sep-21	67.00
60		Oct-21	11.00
61		Nov-21	44.00
62		Dec-21	0.00
63		Total	523.04
64	2022	Jan-22	54.00
65		Feb-22	66.00
66		Mar-22	78.00
67		Apr-22	68.00
68		May-22	-10.00
69		Jun-22	0.00
70		Jul-22	0.00
71		Aug-22	49.00
72		Sep-22	27.00
73		Oct-22	51.00
74		Nov-22	0.00
75		Dec-22	0.00
76		Total	383
77	2023	Jan-23	0.00
78		Feb-23	0.00
79		Mar-23	106.00
80		Apr-23	96.00
81		May-23	46.00
82		Jun-23	188.00
83		Jul-23	0.00
84		Aug-23	139.00
85		Sep-23	0.00
86		Oct-23	0.00
87		Nov-23	0.00
88		Dec-23	0.00
89		Total	575
Sheet2

Cell Formulas
Range	Formula
C11	C11	=SUM(C2:C10)
B25:C36	B25	=Sheet1!P4
B38:C49	B38	=Sheet1!P21
B51:C62	B51	=Sheet1!P38
B64:C75	B64	=Sheet1!P55
B77:C88	B77	=Sheet1!P74
C24,C37,C50,C63,C76,C89	C24	=SUM(C12:C23)

 

[Dave Patton]

Did you consider cleaning your data and trying the suggestion in my post #3?

 

[vladimiratanasiu]

Did you consider cleaning your data and trying the suggestion in my post #3?

Yes, I tried it. Unfortunately, it doesn't work properly. Your solution consider only the range C2:C84, ignoring the range C85:C88 that is automatically filled with value 0 by formulas. And if it did so, the result would be wrong because the average counted the zero values, too. As you can see in my message, I need the formula to extend dynamically, and starting with C25 it should take into account only values from column C that have a numerical equivalent in the external source.

 

[Dave Patton]

It only takes seconds to run the Subtotal.
If you select the relevant range, it yields the correct answer!

 

[vladimiratanasiu]

As I mentioned, I need a formula to run automatically, not by selecting the range manually each time new data are received. I inserted the value 140 in the cell Q82 from Sheet1 and it was imported in the cell C85 of Sheet2. But, as it's seen in the attached table, your formula didn't move down automatically in the cell D85, still keeping its original reference (D84). Please, could you take into account all necessary conditions? Thank you!

Energy.xlsx
	A	B	C	D
77	2023	Jan-23	0.00
78		Feb-23	0.00
79		Mar-23	106.00
80		Apr-23	96.00
81		May-23	46.00
82		Jun-23	188.00
83		Jul-23	0.00
84		Aug-23	139.00	70.07584
85		Sep-23	140.00
86		Oct-23	0.00
87		Nov-23	0.00
88		Dec-23	0.00
89		Total	715.00
Sheet2

Cell Formulas
Range	Formula
D84	D84	=SUBTOTAL(1,C2:C84)
B77:C88	B77	=Sheet1!P74
C89	C89	=SUM(C77:C88)

 

[JamesCanale]

Thank you for the answers! In my previous message, I mentioned an external table as reference source for the imported data, placing it as example in column F of the same sheet. However, the real ground is a bit different. The below table is integrated in the attachment Energy.xlsx, that includes the workbook with real data configuration. Sheet2 has a table, with the costs information in column C. The range C2:C24 includes non-linked information, about years 2017-2018. For years 2019-2023 (C25:C89), the monthly costs are imported automatically by formulas, from the range Q4:Q85 of Sheet1. I kept in the Sheet1 also the real formatted structure of the source table. Considering this new context, I meet a great issue in adjusting your last formula, so that it can integrate and process data from the two different sheets. Could you help me to solve this problem? Thank you!

Energy.xlsx
	A	B	C
1	Year	Month	Costs
2	2017	Apr-17	34.83
3		May-17	72
4		Jun-17	74.77
5		Jul-17	77.74
6		Aug-17	76.5
7		Sep-17	67.74
8		Oct-17	130.11
9		Nov-17	183.44
10		Dec-17	189.56
11		Total	906.69
12	2018	Jan-18	189.56
13		Feb-18	169.62
14		Mar-18	177.41
15		Apr-18	128.08
16		May-18	132.35
17		Jun-18	128.08
18		Jul-18	132.35
19		Aug-18	132.35
20		Sep-18	113.2
21		Oct-18	78.29
22		Nov-18	75.77
23		Dec-18	78.29
24		Total	1535.35
25	2019	Jan-19	78.29
26		Feb-19	70.72
27		Mar-19	71.48
28		Apr-19	64.42
29		May-19	66.57
30		Jun-19	64.42
31		Jul-19	66.57
32		Aug-19	66.57
33		Sep-19	66.29
34		Oct-19	69.79
35		Nov-19	67.54
36		Dec-19	69.79
37		Total	822.45
38	2020	Jan-20	69.79
39		Feb-20	65.29
40		Mar-20	56.51
41		Apr-20	54.27
42		May-20	56.08
43		Jun-20	54.27
44		Jul-20	56.08
45		Aug-20	56.08
46		Sep-20	55.07
47		Oct-20	57.84
48		Nov-20	55.97
49		Dec-20	13.06
50		Total	650.31
51	2021	Jan-21	67.82
52		Feb-21	61.25
53		Mar-21	59.19
54		Apr-21	48.36
55		May-21	49.97
56		Jun-21	48.36
57		Jul-21	49.97
58		Aug-21	16.12
59		Sep-21	67.00
60		Oct-21	11.00
61		Nov-21	44.00
62		Dec-21	0.00
63		Total	523.04
64	2022	Jan-22	54.00
65		Feb-22	66.00
66		Mar-22	78.00
67		Apr-22	68.00
68		May-22	-10.00
69		Jun-22	0.00
70		Jul-22	0.00
71		Aug-22	49.00
72		Sep-22	27.00
73		Oct-22	51.00
74		Nov-22	0.00
75		Dec-22	0.00
76		Total	383
77	2023	Jan-23	0.00
78		Feb-23	0.00
79		Mar-23	106.00
80		Apr-23	96.00
81		May-23	46.00
82		Jun-23	188.00
83		Jul-23	0.00
84		Aug-23	139.00
85		Sep-23	0.00
86		Oct-23	0.00
87		Nov-23	0.00
88		Dec-23	0.00
89		Total	575
Sheet2

Cell Formulas
Range	Formula
C11	C11	=SUM(C2:C10)
B25:C36	B25	=Sheet1!P4
B38:C49	B38	=Sheet1!P21
B51:C62	B51	=Sheet1!P38
B64:C75	B64	=Sheet1!P55
B77:C88	B77	=Sheet1!P74
C24,C37,C50,C63,C76,C89	C24	=SUM(C12:C23)

I have a problem at work with dropbox or any file downloads. Anything you can do to put in xl2bbs in there to articulate your problem will be helpful.

 

[vladimiratanasiu]

I have a problem at work with dropbox or any file downloads. Anything you can do to put in xl2bbs in there to articulate your problem will be helpful.

Ok. I've uploaded each file separated, leaving that you link between sheets the cells connected by formulas indicated in the Sheet2. I have also copied from the Sheet1 a larger section that includes the structure of columns linked by Sheet2, in case that you need their exact configuration. As I mentioned, the range C2:C24 includes non-linked information, about years 2017-2018. Thank you!

Energy.xlsx
														N	O	P	Q	R
1																Electricity
2
3																Period	Costs
4																Jan-19	78.29
5																Feb-19	70.72
6																Mar-19	71.48
7																Apr-19	64.42
8																May-19	66.57
9																Jun-19	64.42
10																Jul-19	66.57
11																Aug-19	66.57
12																Sep-19	66.29
13																Oct-19	69.79
14																Nov-19	67.54
15																Dec-19	69.79
16																Annual total	822.45
17
18
19
20
21																Jan-20	69.79
22																Feb-20	65.29
23																Mar-20	56.51
24																Apr-20	54.27
25																May-20	56.08
26																Jun-20	54.27
27																Jul-20	56.08
28																Aug-20	56.08
29																Sep-20	55.07
30																Oct-20	57.84
31																Nov-20	55.97
32																Dec-20	13.06
33																Annual total	650.31
34
35
36
37
38																Jan-21	67.82
39																Feb-21	61.25
40																Mar-21	59.19
41																Apr-21	48.36
42																May-21	49.97
43																Jun-21	48.36
44																Jul-21	49.97
45																Aug-21	16.12
46																Sep-21	67.00
47																Oct-21	11.00
48																Nov-21	44.00
49																Dec-21	0.00
50																Annual total	523.04
51
52
53
54
55																Jan-22	54.00
56																Feb-22	66.00
57																Mar-22	78.00
58																Apr-22	68.00
59																May-22	-10.00
60																Jun-22	0.00
61																Jul-22	0.00
62																Aug-22	49.00
63																Sep-22	27.00
64																Oct-22	51.00
65																Nov-22	0.00
66																Dec-22	0.00
67																Annual total	383.00
68
69
70
71
72
73
74																Jan-23	0.00
75																Feb-23	0.00
76																Mar-23	106.00
77																Apr-23	96.00
78																May-23	46.00
79																Jun-23	188.00
80																Jul-23	0.00
81																Aug-23	139.00
82																Sep-23
83																Oct-23
84																Nov-23
85																Dec-23
86																Annual total	575.00
87
88
89
90
91
Sheet1

Cell Formulas
Range	Formula
Q16,Q86,Q67,Q50,Q33	Q16	=SUM(Q4:Q15)

Energy.xlsx
	A	B	C
1	Year	Month	Costs
2	2017	Apr-17	34.83
3		May-17	72.00
4		Jun-17	74.77
5		Jul-17	77.74
6		Aug-17	76.50
7		Sep-17	67.74
8		Oct-17	130.11
9		Nov-17	183.44
10		Dec-17	189.56
11		Total	906.69
12	2018	Jan-18	189.56
13		Feb-18	169.62
14		Mar-18	177.41
15		Apr-18	128.08
16		May-18	132.35
17		Jun-18	128.08
18		Jul-18	132.35
19		Aug-18	132.35
20		Sep-18	113.20
21		Oct-18	78.29
22		Nov-18	75.77
23		Dec-18	78.29
24		Total	1,535.35
25	2019	Jan-19	78.29
26		Feb-19	70.72
27		Mar-19	71.48
28		Apr-19	64.42
29		May-19	66.57
30		Jun-19	64.42
31		Jul-19	66.57
32		Aug-19	66.57
33		Sep-19	66.29
34		Oct-19	69.79
35		Nov-19	67.54
36		Dec-19	69.79
37		Total	822.45
38	2020	Jan-20	69.79
39		Feb-20	65.29
40		Mar-20	56.51
41		Apr-20	54.27
42		May-20	56.08
43		Jun-20	54.27
44		Jul-20	56.08
45		Aug-20	56.08
46		Sep-20	55.07
47		Oct-20	57.84
48		Nov-20	55.97
49		Dec-20	13.06
50		Total	650.31
51	2021	Jan-21	67.82
52		Feb-21	61.25
53		Mar-21	59.19
54		Apr-21	48.36
55		May-21	49.97
56		Jun-21	48.36
57		Jul-21	49.97
58		Aug-21	16.12
59		Sep-21	67.00
60		Oct-21	11.00
61		Nov-21	44.00
62		Dec-21	0.00
63		Total	523.04
64	2022	Jan-22	54.00
65		Feb-22	66.00
66		Mar-22	78.00
67		Apr-22	68.00
68		May-22	-10.00
69		Jun-22	0.00
70		Jul-22	0.00
71		Aug-22	49.00
72		Sep-22	27.00
73		Oct-22	51.00
74		Nov-22	0.00
75		Dec-22	0.00
76		Total	383.00
77	2023	Jan-23	0.00
78		Feb-23	0.00
79		Mar-23	106.00
80		Apr-23	96.00
81		May-23	46.00
82		Jun-23	188.00
83		Jul-23	0.00
84		Aug-23	139.00
85		Sep-23	0.00
86		Oct-23	0.00
87		Nov-23	0.00
88		Dec-23	0.00
89		Total	575.00
Sheet2

Cell Formulas
Range	Formula
C11	C11	=SUM(C2:C10)
B25:C36	B25	=Sheet1!P4
B38:C49	B38	=Sheet1!P21
B51:C62	B51	=Sheet1!P38
B64:C75	B64	=Sheet1!P55
B77:C88	B77	=Sheet1!P74
C24,C89,C76,C63,C50,C37	C24	=SUM(C12:C23)