Show average only once

[welshgasman]

Good day all,
I have a worksheet that I use to keep track of my carb intake.
I am happy if I average to a certain value over the week.

I have the sheet below, but is there a way I could just show the average for a week once only instead of 7 times?

TIA

Diabetes.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Carbs	Sugar	Steps	WeekNum	Year	Week Start	Avg Carbs	Avg Sugar	Avg Steps
2	22/03/2023	122.91	62.74	1539	12	2023	20/03/2023	156.28	87.58	1030.50
3	23/03/2023	178.85	58.15	1479	12	2023	20/03/2023	156.28	87.58	1030.50
4	24/03/2023	175.20	94.92	743	12	2023	20/03/2023	156.28	87.58	1030.50
5	25/03/2023	148.17	134.53	361	12	2023	20/03/2023	156.28	87.58	1030.50
6	26/03/2023	110.13	59.79	1380	13	2023	27/03/2023	143.94	50.68	1179.29
7	27/03/2023	136.34	56.24	1051	13	2023	27/03/2023	143.94	50.68	1179.29
8	28/03/2023	160.60	52.16	1587	13	2023	27/03/2023	143.94	50.68	1179.29
9	29/03/2023	139.76	23.91	1458	13	2023	27/03/2023	143.94	50.68	1179.29
10	30/03/2023	137.78	81.64	458	13	2023	27/03/2023	143.94	50.68	1179.29
11	31/03/2023	135.22	33.22	1020	13	2023	27/03/2023	143.94	50.68	1179.29
12	01/04/2023	187.72	47.77	1301	13	2023	27/03/2023	143.94	50.68	1179.29
13	02/04/2023	131.79	99.25	1749	14	2023	03/04/2023	156.24	78.57	1144.86
14	03/04/2023	113.37	65.56	800	14	2023	03/04/2023	156.24	78.57	1144.86
15	04/04/2023	172.07	64.67	899	14	2023	03/04/2023	156.24	78.57	1144.86
16	05/04/2023	135.26	49.50	432	14	2023	03/04/2023	156.24	78.57	1144.86
17	06/04/2023	86.24	41.52	2743	14	2023	03/04/2023	156.24	78.57	1144.86
18	07/04/2023	278.34	166.07	762	14	2023	03/04/2023	156.24	78.57	1144.86
19	08/04/2023	176.60	63.45	629	14	2023	03/04/2023	156.24	78.57	1144.86
20	09/04/2023	185.31	116.44	956	15	2023	10/04/2023	143.00	72.08	1136.71
21	10/04/2023	188.75	65.35	1069	15	2023	10/04/2023	143.00	72.08	1136.71
22	11/04/2023	64.90	9.27	1265	15	2023	10/04/2023	143.00	72.08	1136.71
23	12/04/2023	185.28	66.55	1051	15	2023	10/04/2023	143.00	72.08	1136.71
24	13/04/2023	72.62	53.98	1240	15	2023	10/04/2023	143.00	72.08	1136.71
25	14/04/2023	150.68	42.32	1675	15	2023	10/04/2023	143.00	72.08	1136.71
26	15/04/2023	153.49	150.65	701	15	2023	10/04/2023	143.00	72.08	1136.71
Stats

Cell Formulas
Range	Formula
E2:E26	E2	=WEEKNUM($A2)
F2:F26	F2	=YEAR($A2)
G2:G26	G2	=DATE(F2, 1, -2) - WEEKDAY(DATE(F2, 1, 3)) + E2 * 7
H2:H26	H2	=SUMIF($E:$E,$E2,$B:$B)/COUNTIF($E:$E,$E2)
I2:I26	I2	=SUMIF($E:$E,$E2,$C:$C)/COUNTIF($E:$E,$E2)
J2:J26	J2	=SUMIF($E:$E,$E2,$D:$D)/COUNTIF($E:$E,$E2)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Carbs	Sugar	Steps	WeekNum	Year	Week Start	Avg Carbs	Avg Sugar	Avg Steps
2	22/03/2023	122.91	62.74	1539	12	2023	20/03/2023	156.28	87.58	1030.50
3	23/03/2023	178.85	58.15	1479	12	2023	20/03/2023
4	24/03/2023	175.20	94.92	743	12	2023	20/03/2023
5	25/03/2023	148.17	134.53	361	12	2023	20/03/2023
6	26/03/2023	110.13	59.79	1380	13	2023	27/03/2023	143.94	50.68	1179.29
7	27/03/2023	136.34	56.24	1051	13	2023	27/03/2023
8	28/03/2023	160.60	52.16	1587	13	2023	27/03/2023
9	29/03/2023	139.76	23.91	1458	13	2023	27/03/2023
10	30/03/2023	137.78	81.64	458	13	2023	27/03/2023
11	31/03/2023	135.22	33.22	1020	13	2023	27/03/2023
12	01/04/2023	187.72	47.77	1301	13	2023	27/03/2023
13	02/04/2023	131.79	99.25	1749	14	2023	03/04/2023	156.24	78.57	1144.86
14	03/04/2023	113.37	65.56	800	14	2023	03/04/2023
15	04/04/2023	172.07	64.67	899	14	2023	03/04/2023
16	05/04/2023	135.26	49.50	432	14	2023	03/04/2023
17	06/04/2023	86.24	41.52	2743	14	2023	03/04/2023
18	07/04/2023	278.34	166.07	762	14	2023	03/04/2023
19	08/04/2023	176.60	63.45	629	14	2023	03/04/2023
20	09/04/2023	185.31	116.44	956	15	2023	10/04/2023	143.00	72.08	1136.71
21	10/04/2023	188.75	65.35	1069	15	2023	10/04/2023
22	11/04/2023	64.90	9.27	1265	15	2023	10/04/2023
23	12/04/2023	185.28	66.55	1051	15	2023	10/04/2023
24	13/04/2023	72.62	53.98	1240	15	2023	10/04/2023
25	14/04/2023	150.68	42.32	1675	15	2023	10/04/2023
26	15/04/2023	153.49	150.65	701	15	2023	10/04/2023
Master

Cell Formulas
Range	Formula
E2:E26	E2	=WEEKNUM($A2)
F2:F26	F2	=YEAR($A2)
G2:G26	G2	=DATE(F2, 1, -2) - WEEKDAY(DATE(F2, 1, 3)) + E2 * 7
H2:J26	H2	=IF($E2<>$E1,AVERAGEIFS(B:B,$E:$E,$E2),"")

 

[welshgasman]

That would work nicely. Either the first or last date would be fine.
I did think of a pivot table after I posted and just completed that, but if I can get that display, then that would be better.

Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.