substitute within formula, not string

[lemonbarley]

I have several months' of data in a row, e.g.

	Col A	Col B	Col C	...	Col U	Col V	Col W
		1 Feb 2022	2 Feb 2022	...	28 Feb 2022	1 Mar 2022	2 Mar 2022	...
Row 15	Type A	9984	9182	...	9374	9571	10012	...
Row 16	Type B	24017	23981	...	24231	24523	24917	...

I refresh this data every month with current + future months, so next month, Column B will be 1 Mar 2022.

I need to get the average value of each month. I'm currently using =average() and manually moving the range of cells every month for Type A, I would do this 3x for 3 months. Then I copy the formula to Type B and Ctrl+H Replace the row number from 15 to 16.

Col A	Col B	Col C
Type A	Avg Value	Formula
Feb22	9941	=AVERAGE($B$15:$U$15)
Mar22	9912	=AVERAGE($V$15:$AR$15)
Apr22	9901	=AVERAGE($AS$15:$BM$15)
Type B	Avg Value	Formula
Feb22	24178	=AVERAGE($B$16:$U$16)
Mar22	24578	=AVERAGE($V$16:$AR$16)
Apr22	24440	=AVERAGE($AS$16:$BM$16)

Specific question: Is there a formula (like Substitute) that can do the Ctrl+H on the formula itself? So Type B values will automatically replace 15 to 16 without me having to Ctrl+H it?

Open question: without using Pivot / Power tools / VBA (i.e. only formula), do you have suggestions on how to get the range automatically after data refresh (which is manual copy and paste) without having to manually move the range? Col A are date format so they can be used as date references.

Thanks!

 

[bebo021999]

With date running accross row 14, from B14 to, i.e, ZZ14 (wide enough)
A19 downward is first day of month, formatted as "mmmyy"

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	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO
1
2
3
4
5
6
7
8
9
10
11
12
13
14		01-Feb	02-Feb	03-Feb	04-Feb	05-Feb	06-Feb	07-Feb	08-Feb	09-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	01-Mar	02-Mar	03-Mar	04-Mar	05-Mar	06-Mar	07-Mar	08-Mar	09-Mar	10-Mar	11-Mar	12-Mar	13-Mar	14-Mar	15-Mar	16-Mar	17-Mar	18-Mar	19-Mar	20-Mar	21-Mar	22-Mar	23-Mar	24-Mar	25-Mar	26-Mar	27-Mar	28-Mar	29-Mar	30-Mar	31-Mar	01-Apr	02-Apr	03-Apr	04-Apr	05-Apr	06-Apr	07-Apr
15	Type A	9984	9182	9555	9374	9571	10012	10012	10013	10014	10015	10016	10017	10018	10019	10020	10021	10022	10023	10024	10025	10026	10027	10028	10029	10030	10031	10032	10033	10034	10035	10036	10037	10038	10039	10040	10041	10042	10043	10044	10045	10046	10047	10048	10049	10050	10051	10052	10053	10054	10055	10056	10057	10058	10059	10060	10061	10062	10063	10064	10065	10066	10067	10068	10069	10070	10071
16	Type B	24017	23981	25001	24231	24523	24917	24917	24918	24919	24920	24921	24922	24923	24924	24925	24926	24927	24928	24929	24930	24931	24932	24933	24934	24935	24936	24937	24938	24939	24940	24941	24942	24943	24944	24945	24946	24947	24948	24949	24950	24951	24952	24953	24954	24955	24956	24957	24958	24959	24960	24961	24962	24963	24964	24965	24966	24967	24968	24969	24970	24971	24972	24973	24974	24975	24976
17
18	Type A	AVG
19	Feb22	9,935
20	Mar22	10,049
21	Apr22	2,349
22
23	Type B	AVG
24	Feb22	24,824
25	Mar22	24,954
26	Apr22	5,827
Sheet4

Cell Formulas
Range	Formula
I14:BO16,C14:H14	C14	=B14+1
H15:H16	H15	=G15
B24:B26,B19:B21	B19	=SUMPRODUCT(($A$15:$A$16=INDEX($A$18:$A19,AGGREGATE(14,6,MATCH($A$15:$A$16,$A$18:$A19,0),1)))*($B$14:$ZZ$14>=$A19)*($B$14:$ZZ$14<=EOMONTH($A19,0))*$B$15:$ZZ$16)/DAY(EOMONTH($A19,0))

 

[BSALV]

average works with the number of filled cells, suppose you have only data in the week, not in the weekends, then you make a mistake with the formula above.

 

[BSALV]

with a named range "My_dates" and now i see that there aren't enough columns for 3 entire months

Map1
	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	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO
14		1/02/22	2/02/22	3/02/22	4/02/22	5/02/22	6/02/22	7/02/22	8/02/22	9/02/22	10/02/22	11/02/22	12/02/22	13/02/22	14/02/22	15/02/22	16/02/22	17/02/22	18/02/22	19/02/22	20/02/22	21/02/22	22/02/22	23/02/22	24/02/22	25/02/22	26/02/22	27/02/22	28/02/22	1/03/22	2/03/22	3/03/22	4/03/22	5/03/22	6/03/22	7/03/22	8/03/22	9/03/22	10/03/22	11/03/22	12/03/22	13/03/22	14/03/22	15/03/22	16/03/22	17/03/22	18/03/22	19/03/22	20/03/22	21/03/22	22/03/22	23/03/22	24/03/22	25/03/22	26/03/22	27/03/22	28/03/22	29/03/22	30/03/22	31/03/22	1/04/22	2/04/22	3/04/22	4/04/22	5/04/22	6/04/22	7/04/22
15	Type A	9984	9182	9555	9374	9571	10012	10012	10013	10014	10015	10016	10017	10018	10019	10020	10021	10022	10023	10024	10025	10026	10027	10028	10029	10030	10031	10032	10033	10034	10035	10036	10037	10038	10039	10040	10041	10042	10043	10044	10045	10046	10047	10048	10049	10050	10051	10052	10053	10054	10055	10056	10057	10058	10059	10060	10061	10062	10063	10064	10065	10066	10067	10068	10069	10070	10071
16	Type B	24017	23981	25001	24231	24523	24917	24917	24918	24919	24920	24921	24922	24923	24924	24925	24926	24927	24928	24929	24930	24931	24932	24933	24934	24935	24936	24937	24938	24939	24940	24941	24942	24943	24944	24945	24946	24947	24948	24949	24950	24951	24952	24953	24954	24955	24956	24957	24958	24959	24960	24961	24962	24963	24964	24965	24966	24967	24968	24969	24970	24971	24972	24973	24974	24975	24976
17
18	Type A	AVG
19	01-02-22	9.934,8
20	01-03-22	10.049,0
21	01-04-22	10.068,0
22
23	Type B	AVG
24	01-02-22	24.824,1
25	01-03-22	24.954,0
26	01-04-22	24.973,0
Blad4

Cell Formulas
Range	Formula
C14:BO14,I15:BO16	C14	=B14+1
H15:H16	H15	=G15
A19,A24	A19	=+$B$14
B19:B21	B19	=IFERROR(SUMPRODUCT(--(MONTH(My_dates)=MONTH(A19))*OFFSET(My_dates,1,,,))/SUMPRODUCT(--(MONTH(My_dates)=MONTH(A19))*(OFFSET(My_dates,1,,,)<>"")),"-")
A20:A21,A25:A26	A20	=EDATE(A19,1)
B24:B26	B24	=IFERROR(SUMPRODUCT(--(MONTH(My_dates)=MONTH(A24))*OFFSET(My_dates,2,,,))/SUMPRODUCT(--(MONTH(My_dates)=MONTH(A24))*(OFFSET(My_dates,2,,,)<>"")),"-")

Named Ranges
Name	Refers To	Cells
My_dates	=Blad4!$B$14:$BO$14	B19:B21, B24:B26, C14, A24, A19

 

[Fluff]

Another option

+Fluff 1.xlsm
	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	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO
14		01/02/2022	02/02/2022	03/02/2022	04/02/2022	05/02/2022	06/02/2022	07/02/2022	08/02/2022	09/02/2022	10/02/2022	11/02/2022	12/02/2022	13/02/2022	14/02/2022	15/02/2022	16/02/2022	17/02/2022	18/02/2022	19/02/2022	20/02/2022	21/02/2022	22/02/2022	23/02/2022	24/02/2022	25/02/2022	26/02/2022	27/02/2022	28/02/2022	01/03/2022	02/03/2022	03/03/2022	04/03/2022	05/03/2022	06/03/2022	07/03/2022	08/03/2022	09/03/2022	10/03/2022	11/03/2022	12/03/2022	13/03/2022	14/03/2022	15/03/2022	16/03/2022	17/03/2022	18/03/2022	19/03/2022	20/03/2022	21/03/2022	22/03/2022	23/03/2022	24/03/2022	25/03/2022	26/03/2022	27/03/2022	28/03/2022	29/03/2022	30/03/2022	31/03/2022	01/04/2022	02/04/2022	03/04/2022	04/04/2022	05/04/2022	06/04/2022	07/04/2022
15	Type A	9984	9182	9555	9374	9571	10012	10012	10013	10014	10015	10016	10017	10018	10019	10020	10021	10022	10023	10024	10025	10026	10027	10028	10029	10030	10031	10032	10033	10034	10035	10036	10037	10038	10039	10040	10041	10042	10043	10044	10045	10046	10047	10048	10049	10050	10051	10052	10053	10054	10055	10056	10057	10058	10059	10060	10061	10062	10063	10064	10065	10066	10067	10068	10069	10070	10071
16	Type B	24017	23981	25001	24231	24523	24917	24917	24918	24919	24920	24921	24922	24923	24924	24925	24926	24927	24928	24929	24930	24931	24932	24933	24934	24935	24936	24937	24938	24939	24940	24941	24942	24943	24944	24945	24946	24947	24948	24949	24950	24951	24952	24953	24954	24955	24956	24957	24958	24959	24960	24961	24962	24963	24964	24965	24966	24967	24968	24969	24970	24971	24972	24973	24974	24975	24976
17
18	Type A	AVG
19	01/02/2022	9934.75
20	01/03/2022	10049
21	01/04/2022	10068
Data

Cell Formulas
Range	Formula
C14:BO14,I15:BO16	C14	=B14+1
H15:H16	H15	=G15
B19:B21	B19	=AVERAGEIFS($B$15:$BO$15,$B$14:$BO$14,">="&A19,$B$14:$BO$14,"<"&EDATE(A19,1))

 

[kvsrinivasamurthy]

Data in Sheet1
In Sheet2

In the cell( Say A4) "Feb22" replace By date 1/2/22(1 feb 22) format as MmmYY, if required.

For result

Row2	ColA	Col B	Col C
Row3		Type A	Type B
Row4	01-02-2022​	=AVERAGEIFS(OFFSET(Sheet1!$B15:$AZ15,COLUMNS($B4:B4),0),Sheet1!$B$14:$AZ$14,"<="&EOMONTH($A4,0),Sheet1!$B$14:$AZ$14,">="&$A4)
Row5	01-03-2022​

Copy Formula in B4 across and down .

 

[lemonbarley]

Thank you everyone for your inputs! Sorry I came to this only one month later when I needed it ?

I picked @Fluff 's solution because it is so simple and would have given me what I needed - except that I realised my data is in a table* and the headers are not recognised as dates when I paste them in, so the >= and <edate() did not work. I have an extra row so I put month() in there, which does work (???), then averageifs using the month() row.

=AVERAGEIFS($B$15:$BO$15,$B$14:$BO$14,">="&A19,$B$14:$BO$14,"<"&EDATE(A19,1))
became
=AVERAGEIFS($B$15:$BO$15,$B$13:$BO$13,month(A19))
where Row 13 is =month(row 14)

Then I saw that @BSALV 's solution uses a named range which I can use to integrate the month() into the formula, so I need not use that extra row. Btw, astute observation that there could be weekends without data and indeed I don't have values on non-business days. However, instead of "", those days have "N/A". While "N/A" are automatically excluded in =average which is why I could use it in the first place, it throws up an error when I put it in your formula.

=IFERROR(SUMPRODUCT(--(MONTH(My_dates)=MONTH(A19))*OFFSET(My_dates,1,,,))/SUMPRODUCT(--(MONTH(My_dates)=MONTH(A19))*(OFFSET(My_dates,1,,,)<>"")),"-")
Even when I changed the <>"" to <>"N/A", I get #VALUE! when I take out iferror

*I used a table because I use power tools to get the averages to check my numbers. I still needed the formula because of data presentation and I want to be able to pass this on to new users who may not know how to troubleshoot if the pivots go haywire.

 

[BSALV]

still another "simple" solution
there can be errors in the range of the Types
Here below in the named range, TypeA and TypeB aren't mentioned, but they are offset(mydates,1,,,) and offset(mydates,2,,,)
An empty cell isn't used in that average

Map1
	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	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO
14		01-feb	02-feb	03-feb	04-feb	05-feb	06-feb	07-feb	08-feb	09-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	01-mrt	02-mrt	03-mrt	04-mrt	05-mrt	06-mrt	07-mrt	08-mrt	09-mrt	10-mrt	11-mrt	12-mrt	13-mrt	14-mrt	15-mrt	16-mrt	17-mrt	18-mrt	19-mrt	20-mrt	21-mrt	22-mrt	23-mrt	24-mrt	25-mrt	26-mrt	27-mrt	28-mrt	29-mrt	30-mrt	31-mrt	01-apr	02-apr	03-apr	04-apr	05-apr	06-apr	07-apr
15	Type A	9984,00	9182,00	9555,00	9374,00	9571,00	10012,00	#N/B	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00
16	Type B	24017,00	23981,00	25001,00	24231,00	24523,00	24917,00	#N/B	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	1,00	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	1,00	1,00	1,00	1,00	1,00	1,00	1,00
17
18	Type A	AVG
19	feb22	2137
20	mrt22	1
21	apr22	1
22
23	Type B	AVG
24	feb22	5433
25	mrt22	no data
26	apr22	1
Blad2

Cell Formulas
Range	Formula
C14:BO14	C14	=+B14+1
H15:H16,AD16:BH16	H15	=NA()
I15:BO15,I16:AC16,BI16:BO16	I15	=H28+1
A19,A24	A19	=+$B$14
B19:B21	B19	=AVERAGE(IF(ISNUMBER(TypeA),IF(MyDates-DAY(MyDates)+1=A19,TypeA)))
A20:A21,A25:A26	A20	=EDATE(A19,1)
B24,B26	B24	=AVERAGE(IF(ISNUMBER(TypeB),IF(MyDates-DAY(MyDates)+1=A24,TypeB)))
B25	B25	=IFERROR(AVERAGE(IF(ISNUMBER(TypeB),IF(MyDates-DAY(MyDates)+1=A25,TypeB))),"no data")

Named Ranges
Name	Refers To	Cells
MyDates	=Blad2!$B$14:$BO$14	B19:B21, B24:B26, C14, A24, A19

 

[BSALV]

an improvement for A19 to be sure that is the 1st day of that month

Map1
	A	B
18	Type A	AVG
19	feb22	2136,96296
20	mrt22	1
21	apr22	1
Blad2

Cell Formulas
Range	Formula
A19	A19	=+$B$14-DAY(B14)+1
B19:B21	B19	=AVERAGE(IF(ISNUMBER(TypeA),IF(MyDates-DAY(MyDates)+1=A19,TypeA)))
A20:A21	A20	=EDATE(A19,1)

Named Ranges
Name	Refers To	Cells
MyDates	=Blad2!$B$14:$BO$14	B19:B21, A19