AVG Function not Updating Range

[tperk100]

Using AVG function across Row 62. If I insert new Row at 61, and then insert values in Row 61,
Row 63 functions update to include Row 61 range and values, EXCEPT G63 does not update.

Seems like a simple problem, but I am baffled. Help will be appreciated. Is this mini sheet uploaded correctly?

Heart Log.xls
	A	B	C	D	E	F	G
4	Date	Time	Sys	Dia	Pulse	Glucose	Weight
5
6
7	10/5/22	0900	110	83	60	86
8	10/6/22	0900	116	80	61	121
9	10/7/22	0900	131	79	72
10	10/8/22	1000	118	86	67
11	10/9/22	1100	136	97	66
12		1105	133	91	64
13	10/10/22	0830	140	78	70
14	10/11/22	0930	145	74	65
15	10/12/22	0900	130	86	74
16	10/13/22	1030	122	75	66
17	10/14/22	0900	125	75	59
18	10/15/22	0700	145	90	68
19		0730	111	70	74
20	10/16/22	1030	126	77	64	130
21	10/17/22	0930	120	86	64	118
22	10/18/22	0900	123	85	68	121
23	10/19/22	0930	137	86	59	118
24	10/20/22	1000	106	81	76	163
25		1005	123	90	61	171
26		1600	120	86	61	131
27	10/21/22	1200	117	91	69	120
28	10/22/22	0930	122	78	59	105
29	10/23/22	1300	130	76	71	120
30	10/24/22	0930	143	79	65
31		1435	122	84	63
32	10/25/22	1000	126	87	75	125
33	10/26/22	0700	123	77	61
34	10/27/22	0930	134	81	86	92
35	10/28/22	1130	151	86	54
36		1135	116	97	62	118
37	10/29/22	0800	115	88	72	115
38	10/30/22	1000	124	84	61	98
39	10/31/22	1000	124	86	69	134	229
40	11/1/22	0530	132	86	80	97
41	11/2/22	1130	128	85	76	105
42	11/4/22	0830	112	82	67	94	228
43	11/6/22	0800	118	81	64	87
44	11/8/22	0900	102	71	79	129
45	11/9/22	0800	117	72	40
46	11/10/22	0800	117	83	71	118
47		0805	134	67	75	119
48	11/11/22	0800	194	88	81
49		0810	130	90	69	100
50	11/12/22	0800	112	74	77	129
51	11/14/22	0830	116	71	68	111
52	11/16/22	0800	110	82	138	183
53		0810	128	81	84	186
54	11/17/22	1000	146	82	68	116	240
55		1005	132	72	88
56	11/18/22	1000	138	75	70	122
57	11/19/22	0900	134	83	64	120
58	11/21/22	0900	130	97	68
59	11/24/22	0900	141	86	56	88
60	11/27/22	1000	123	85	61	113
61
62	Average		127	82	69	120	232
Sheet1

Cell Formulas
Range	Formula
C62:F62	C62	=AVERAGE(C7:C60)
G62	G62	=AVERAGE(G7:G59)

 

[RobP]

Your G62 formula only looks up to row59 so will ignore whatever you do on row 60 or 61. You need it to look to row 61 I believe (AVERAGE(G7:G61) even though its blank.

 

[tperk100]

Your G62 formula only looks up to row59 so will ignore whatever you do on row 60 or 61. You need it to look to row 61 I believe (AVERAGE(G7:G61) even though its blank.

If I insert new Row at 61, and then insert values in Row 61,
Row 63 functions update to include Row 61 range and values, EXCEPT G63 does not update.

 

[Automatrix]

You should consider arranging your data in a named table. That way you would be referring to Table1[Sys] regardless of how many rows it contains.

Book2
	A	B	C	D	E	F	G
1	Date	Time	Sys	Dia	Pulse	Glucose	Weight
2	44839	900	110	83	60	86
3	44840	900	116	80	61	121
4	44841	900	131	79	72
5	44842	1000	118	86	67
6	44843	1100	136	97	66
7		1105	133	91	64
8	44844	830	140	78	70
9	44845	930	145	74	65
10	44846	900	130	86	74
11	44847	1030	122	75	66
12	44848	900	125	75	59
13	44849	700	145	90	68
14		730	111	70	74
15	44850	1030	126	77	64	130
16	44851	930	120	86	64	118
17	44852	900	123	85	68	121
18	44853	930	137	86	59	118
19	44854	1000	106	81	76	163
20		1005	123	90	61	171
21		1600	120	86	61	131
22	44855	1200	117	91	69	120
23	44856	930	122	78	59	105
24	44857	1300	130	76	71	120
25	44858	930	143	79	65
26		1435	122	84	63
27	44859	1000	126	87	75	125
28	44860	700	123	77	61
29	44861	930	134	81	86	92
30	44862	1130	151	86	54
31		1135	116	97	62	118
32	44863	800	115	88	72	115
33	44864	1000	124	84	61	98
34	44865	1000	124	86	69	134	229
35	44866	530	132	86	80	97
36	44867	1130	128	85	76	105
37	44869	830	112	82	67	94	228
38	44871	800	118	81	64	87
39	44873	900	102	71	79	129
40	44874	800	117	72	40
41	44875	800	117	83	71	118
42		805	134	67	75	119
43	44876	800	194	88	81
44		810	130	90	69	100
45	44877	800	112	74	77	129
46	44879	830	116	71	68	111
47	44881	800	110	82	138	183
48		810	128	81	84	186
49	44882	1000	146	82	68	116	240
50		1005	132	72	88
51	44883	1000	138	75	70	122
52	44884	900	134	83	64	120
53	44886	900	130	97	68
54	44889	900	141	86	56	88
55	44892	1000	123	85	61	113
56
57	Average		127	82,25926	69,07407	120,0857	232,3333
Sheet1

Cell Formulas
Range	Formula
C57	C57	=AVERAGE(Table1[Sys])
D57	D57	=AVERAGE(Table1[Dia])
E57	E57	=AVERAGE(Table1[Pulse])
F57	F57	=AVERAGE(Table1[Glucose])
G57	G57	=AVERAGE(Table1[Weight])

 

[tperk100]

You should consider arranging your data in a named table. That way you would be referring to Table1[Sys] regardless of how many rows it contains.

Book2
	A	B	C	D	E	F	G
1	Date	Time	Sys	Dia	Pulse	Glucose	Weight
2	44839	900	110	83	60	86
3	44840	900	116	80	61	121
4	44841	900	131	79	72
5	44842	1000	118	86	67
6	44843	1100	136	97	66
7		1105	133	91	64
8	44844	830	140	78	70
9	44845	930	145	74	65
10	44846	900	130	86	74
11	44847	1030	122	75	66
12	44848	900	125	75	59
13	44849	700	145	90	68
14		730	111	70	74
15	44850	1030	126	77	64	130
16	44851	930	120	86	64	118
17	44852	900	123	85	68	121
18	44853	930	137	86	59	118
19	44854	1000	106	81	76	163
20		1005	123	90	61	171
21		1600	120	86	61	131
22	44855	1200	117	91	69	120
23	44856	930	122	78	59	105
24	44857	1300	130	76	71	120
25	44858	930	143	79	65
26		1435	122	84	63
27	44859	1000	126	87	75	125
28	44860	700	123	77	61
29	44861	930	134	81	86	92
30	44862	1130	151	86	54
31		1135	116	97	62	118
32	44863	800	115	88	72	115
33	44864	1000	124	84	61	98
34	44865	1000	124	86	69	134	229
35	44866	530	132	86	80	97
36	44867	1130	128	85	76	105
37	44869	830	112	82	67	94	228
38	44871	800	118	81	64	87
39	44873	900	102	71	79	129
40	44874	800	117	72	40
41	44875	800	117	83	71	118
42		805	134	67	75	119
43	44876	800	194	88	81
44		810	130	90	69	100
45	44877	800	112	74	77	129
46	44879	830	116	71	68	111
47	44881	800	110	82	138	183
48		810	128	81	84	186
49	44882	1000	146	82	68	116	240
50		1005	132	72	88
51	44883	1000	138	75	70	122
52	44884	900	134	83	64	120
53	44886	900	130	97	68
54	44889	900	141	86	56	88
55	44892	1000	123	85	61	113
56
57	Average		127	82,25926	69,07407	120,0857	232,3333
Sheet1

Cell Formulas
Range	Formula
C57	C57	=AVERAGE(Table1[Sys])
D57	D57	=AVERAGE(Table1[Dia])
E57	E57	=AVERAGE(Table1[Pulse])
F57	F57	=AVERAGE(Table1[Glucose])
G57	G57	=AVERAGE(Table1[Weight])

I wish I knew enough to understand and implement what your trying to show me

 

[Automatrix]

I wish I knew enough to understand and implement what your trying to show me

Highlight your data (not including the averages), go to Insert and click "Table". Keep the checkmark. You have now created your first table, which is a very handy thing in Excel

 

[tperk100]

Highlight your data (not including the averages), go to Insert and click "Table". Keep the checkmark. You have now created your first table, which is a very handy thing in Excel

Did that. Header text disappeared . dropdown arrow still there. Otherwise seems to be working How to get header text back?

 

[Automatrix]

Did that. Header text disappeared . dropdown arrow still there. Otherwise seems to be working How to get header text back?

Hard to tell without a screenshot.

 

[RoryA]

Most likely just need to change the font colour

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

AVG Function not Updating Range

Using AVG function across Row 62. If I insert new Row at 61, and then insert values in Row 61, Row 63 functions update to include Row 61 range and values, EXCEPT G63 does not update. Seems like a simple problem, but I am baffled. Help will be appreciated.

forums.excelguru.ca

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.