Excel Formula To Average all numbers in one month from list of dates

[JEF13]

I have three years of dates starting in A2 and running through A984. In cells B2 through B984 I have a list of values.

A sample of the date looks like:

5/17/2023 90
5/18/2023 88
5/19/2023 89
5/20/2023 90
5/21/2023 91
5/22/2023 93
5/23/2023 91
5/24/2023 89
5/25/2023 90
5/26/2023 87
5/27/2023 90
5/28/2023 83
5/29/2023 85

Is there a formula that can:

1. Provide an average for all the numbers that are in one month
2. Create two new entries:
A. The month
B. The average for the month

The output would look like:

05-2023 92
06-2023 89

 

[awoohaw]

Try this:
I just realizzed this uses text after and text before which are not in 2019. I hope you can figure out a parsing formula for the date and value column.
Although, I really unsure if you have all the data in one column (or two already).

Mr excel questions 66.xlsm
	A	B	C	D	E	F	G
1					Month/Year	Average
2	5/17/2023 90	2023-05-17	90		2023-05	88.9230769
3	5/18/2023 88	2023-05-18	88		2023-04	90.3333333
4	5/19/2023 89	2023-05-19	89		2023-03	87.3333333
5	5/20/2023 90	2023-05-20	90
6	5/21/2023 91	2023-05-21	91
7	5/22/2023 93	2023-05-22	93
8	5/23/2023 91	2023-05-23	91
9	5/24/2023 89	2023-05-24	89
10	5/25/2023 90	2023-05-25	90
11	5/26/2023 87	2023-05-26	87
12	5/27/2023 90	2023-05-27	90
13	5/28/2023 83	2023-05-28	83
14	5/29/2023 85	2023-05-29	85
15	4/18/2023 88	2023-04-18	88
16	4/19/2023 89	2023-04-19	89
17	4/20/2023 90	2023-04-20	90
18	4/21/2023 91	2023-04-21	91
19	4/22/2023 93	2023-04-22	93
20	4/23/2023 91	2023-04-23	91
21	3/24/2023 89	2023-03-24	89
22	3/23/2023 90	2023-03-23	90
23	3/26/2023 87	2023-03-26	87
24	3/27/2023 90	2023-03-27	90
25	3/28/2023 83	2023-03-28	83
26	3/29/2023 85	2023-03-29	85
JEF13

Cell Formulas
Range	Formula
B2:B26	B2	=DATE(TEXTAFTER(TEXTBEFORE(A2," "),"/",2),TEXTBEFORE(A2,"/",1),TEXTAFTER(TEXTBEFORE(A2,"/",2),"/"))
C2:C26	C2	=TEXTAFTER(A2," ")*1
E2	E2	=EOMONTH(MAX(B2:B26),-1)+1
F2:F4	F2	=SUM(((MONTH(E2)=MONTH($B$2:$B$26))*(YEAR(E2)=YEAR($B$2:$B$26))*($C$2:$C$26)))/ SUM((MONTH(E2)=MONTH($B$2:$B$26))*(YEAR(E2)=YEAR($B$2:$B$26)))
E3:E4	E3	=EDATE(E2,-1)

 

[Dave Patton]

T202310a.xlsm
	B	C	D	E	F
1
2	17-May-23	90		01-Mar-2023	87.33
3	18-May-23	88		Apr-23	90.33
4	19-May-23	89		May-23	88.92
5	20-May-23	90
6	21-May-23	91
7	22-May-23	93
8	23-May-23	91
9	24-May-23	89
10	25-May-23	90
11	26-May-23	87
12	27-May-23	90
13	28-May-23	83
14	29-May-23	85
15	18-Apr-23	88
16	19-Apr-23	89
17	20-Apr-23	90
18	21-Apr-23	91
19	22-Apr-23	93
20	23-Apr-23	91
21	24-Mar-23	89
22	23-Mar-23	90
23	26-Mar-23	87
24	27-Mar-23	90
25	28-Mar-23	83
26	29-Mar-23	85
2a

Cell Formulas
Range	Formula
F2:F4	F2	=AVERAGEIFS($C$2:$C$26,$B$2:$B$26,">="&E2,$B$2:$B$26,"<="&EOMONTH(E2,0))