Calculate monthly average and mid month average?

[lichldo]

Hello, I have a set of daily numbers (excluding weekends) for every day of every month. At the end of the month, I need to calculate the percentage change from the first day of the month to the last day, and also the mid-month percentage change (from the middle day of the month to the last day of the month)

I'm wondering if there is a formula I can easily apply to all of my rows based on how my data is set up so that I don't have to adjust each months formula based on when the first day of the month is and to adjust for when the last day is (as some months have 30 days vs 31 days etc)

I've attached a sample sheet so you can see how it's set up - it's done this way so that we can do an easy copy paste from the system this data comes from, I understand it's not the most ideal way to format it, but it's the easiest way for us

The formulas are in Columns AG and AH

Sample Sheet - Averages.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	PM	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	9-Jan	10-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	16-Jan	17-Jan	18-Jan	19-Jan	20-Jan	21-Jan	22-Jan	23-Jan	24-Jan	25-Jan	26-Jan	27-Jan	28-Jan	29-Jan	30-Jan	31-Jan	Mid Month Difference	Month Difference
2	A			47,829.41	47,829.41	47,829.41	47,829.41	47,829.41			47,829.41	47,829.41	39,755.00	39,755.00	39,755.00			39755	39755	39,755.00	39,755.00	46,935.00			46,935.00	46,935.00	46,935.00	46,935.00	66,220.71			64,320.71	61.79%	N/A
3	B			93,027.45	93,027.45	71,062.41	71,062.41	69,517.97			58,011.86	58,011.86	58,011.86	58,011.86	58,011.86			58011.86	58011.86	58,011.86	58,011.86	58,011.86			58,280.61	57,245.66	57,245.66	56,178.54	55,756.58			55,756.58	-3.89%	N/A
4	C																																N/A	N/A
5	D																																N/A	N/A
6	E																																N/A	N/A
7	F																																N/A	N/A
8	G																																N/A	N/A
9	H																																N/A	N/A
10	I																																N/A	N/A
11	J																																N/A	N/A
12	K																																N/A	N/A
13	L																																N/A	N/A
14	M																																N/A	N/A
15	N																																N/A	N/A
16	O																																N/A	N/A
17	P																																N/A	N/A
18	Q																																N/A	N/A
19	R																																N/A	N/A
20	S																																N/A	N/A
21	T																																N/A	N/A
22	U																																N/A	N/A
23	V																																N/A	N/A
24	W																																N/A	N/A
25	X																																N/A	N/A
26	Y																																N/A	N/A
27	Z																																N/A	N/A
28	AA																																N/A	N/A
29	AB																																N/A	N/A
30
31	PM	1-Feb	2-Feb	3-Feb	4-Feb	5-Feb	6-Feb	7-Feb	8-Feb	9-Feb	10-Feb	11-Feb	12-Feb	13-Feb	14-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	20-Feb	21-Feb	22-Feb	23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb				Mid Month Difference	Month Difference
32	A	104,502.35	17,175.00	17,175.00	17,175.00			17,175.00	11,775.00	11,775.00	11,775.00	11,775.00			11,775.00	11,775.00	11,775.00	11,775.00	11,775.00			11,775.00	11,775.00	11,775.00	11,775.00	9,075.00			9,075.00				-22.93%	-91.32%
33	B	125,987.79	125,987.79	125,987.79	125,987.79			125,987.79	74,109.35	74,603.38	74,603.38	74,603.38			74,603.38	68,565.56	68,565.56	66,359.55	66,529.55			63,811.63	63,811.63	63,811.63	63,534.96	63,412.65			63,412.65				-15.00%	-49.67%
34	C																																N/A	N/A
35	D																																N/A	N/A
36	E																																N/A	N/A
37	F																																N/A	N/A
38	G																																N/A	N/A
39	H																																N/A	N/A
40	I																																N/A	N/A
41	J																																N/A	N/A
42	K																																N/A	N/A
43	L																																N/A	N/A
44	M																																N/A	N/A
45	N																																N/A	N/A
46	O																																N/A	N/A
47	P																																N/A	N/A
48	Q																																N/A	N/A
49	R																																N/A	N/A
50	S																																N/A	N/A
51	T																																N/A	N/A
52	U																																N/A	N/A
53	V																																N/A	N/A
54	W																																N/A	N/A
55	X																																N/A	N/A
56	Y																																N/A	N/A
57	Z																																N/A	N/A
58	AA																																N/A	N/A
59	AB																																N/A	N/A
Sheet1

Cell Formulas
Range	Formula
C1:AF1,C31:AF31	C1	=IF(B1="","",IF(MONTH(B1+1)<>MONTH(B1),"",B1+1))
AG2:AG29	AG2	=IFERROR((AF2-O2)/O2,"N/A")
AH2:AH29	AH2	=IFERROR((AF2-B2)/B2,"N/A")
AG32:AG59	AG32	=IFERROR((AC32-O32)/O32,"N/A")
AH32:AH59	AH32	=IFERROR((AC32-B32)/B32,"N/A")

 

[Habtest]

Hi, try this for "Month Difference" column. How do you define the "Mid Month day" in relation to the total month days, and what if it's a weekend?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	PM	2022/1/1	2022/1/2	2022/1/3	2022/1/4	2022/1/5	2022/1/6	2022/1/7	2022/1/8	2022/1/9	2022/1/10	2022/1/11	2022/1/12	2022/1/13	2022/1/14	2022/1/15	2022/1/16	2022/1/17	2022/1/18	2022/1/19	2022/1/20	2022/1/21	2022/1/22	2022/1/23	2022/1/24	2022/1/25	2022/1/26	2022/1/27	2022/1/28	2022/1/29	2022/1/30	2022/1/31	Mid Month Difference	Month Difference
2	A			47,829.41	47,829.41	47,829.41	47,829.41	47,829.41			47,829.41	47,829.41	39,755.00	39,755.00	39,755.00			39,755.00	39,755.00	39,755.00	39,755.00	46,935.00			46,935.00	46,935.00	46,935.00	46,935.00	66,220.71			64,320.71		34.48%
3	B			93,027.45	93,027.45	71,062.41	71,062.41	69,517.97			58,011.86	58,011.86	58,011.86	58,011.86	58,011.86			58,011.86	58,011.86	58,011.86	58,011.86	58,011.86			58,280.61	57,245.66	57,245.66	56,178.54	55,756.58			55,756.58		-40.06%
4	C
30
31	PM	2022/2/1	2022/2/2	2022/2/3	2022/2/4	2022/2/5	2022/2/6	2022/2/7	2022/2/8	2022/2/9	2022/2/10	2022/2/11	2022/2/12	2022/2/13	2022/2/14	2022/2/15	2022/2/16	2022/2/17	2022/2/18	2022/2/19	2022/2/20	2022/2/21	2022/2/22	2022/2/23	2022/2/24	2022/2/25	2022/2/26	2022/2/27	2022/2/28				Mid Month Difference	Month Difference
32	A	104,502.35	17,175.00	17,175.00	17,175.00			17,175.00	11,775.00	11,775.00	11,775.00	11,775.00			11,775.00	11,775.00	11,775.00	11,775.00	11,775.00			11,775.00	11,775.00	11,775.00	11,775.00	9,075.00			9,075.00					-91.32%
33	B	125,987.79	125,987.79	125,987.79	125,987.79			125,987.79	74,109.35	74,603.38	74,603.38	74,603.38			74,603.38	68,565.56	68,565.56	66,359.55	66,529.55			63,811.63	63,811.63	63,811.63	63,534.96	63,412.65			63,412.65					-49.67%
34	C
Sheet1

Cell Formulas
Range	Formula
C31:AF31,C1:AF1	C1	=IF(B1="","",IF(MONTH(B1+1)<>MONTH(B1),"",B1+1))
AH2,AH32:AH33	AH2	=LOOKUP(9^9,B2:AF2)/INDEX(B2:AF2,MATCH(TRUE,INDEX(ISNUMBER(B2:AF2),0),0))-1
AH3	AH3	=LOOKUP(9^9,A3:AE3)/INDEX(A3:AE3,MATCH(TRUE,INDEX(ISNUMBER(A3:AE3),0),0))-1