VBA code required to conclude average margin

[exceluser9]

Hi Team,

I have below data in 2 sheets. I have week numbers in both the sheet. In sheet 1 i require the average margin in column "F" (Result column)

Based on the start week and end week it has to pull the average margin from sheet 2 for from whichever week falls between start week and end week based on the lookup from group id

I have just posted an example but there are multiple data in sheet 1 and 2

Sheet 1
Start date	Start week	End date	End week	Group id	Result
11/02/2022	202249	06/03/2022	202253	X51AG	57%
05/01/2022	202228	18/01/2022	202233	XH7GY	45%

Sheet 2
Week	Group id	Margin
202245	X51AG	56%
202249	X51AG	67%
202252	X51AG	44%
202254	X51AG	61%
202232	XH7GY	56%
202227	XH7GY	33%

 

[exceluser9]

Hi Team,

Can anyone help?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this the sort of thing you mean?
Not the same answers as you have but I'm assuming that is because you have not shown full data.
If this is not what you want then please give some sample data and expected results with sufficient information and explanation as to how you manually get the results.

22 04 14.xlsm
	A	B	C	D	E	F
1	Sheet 1
2	Start date	Start week	End date	End week	Group id	Result
3	11/02/2022	202249	6/03/2022	202253	X51AG	55.5%
4	5/01/2022	202228	18/01/2022	202233	XH7GY	56.0%
5
6
7	Sheet 2
8	Week	Group id	Margin
9	202245	X51AG	56%
10	202249	X51AG	67%
11	202252	X51AG	44%
12	202254	X51AG	61%
13	202232	XH7GY	56%
14	202227	XH7GY	33%
Average

Cell Formulas
Range	Formula
F3:F4	F3	=AVERAGEIFS(C$9:C$14,A$9:A$14,">="&B3,A$9:A$14,"<="&D3)

 

[exceluser9]

Hi Peter,

I have checked the formula which you have provided. However, i do not see any validation against the group id. I have provided one more example below

Please could you provide validation for group id as well?

Sheet 1
Start date	Start week	End date	End week	Group id	Result
11/02/2022	202249	06/03/2022	202252	X51AG	40%
05/01/2022	202228	18/01/2022	202233	XH7GY	56%
13/01/2022	202229	22/02/2011​	202231	X51AG	34%

Week	Group id	Margin
202245	X51AG	56%
202249	X51AG	36%
202252	X51AG	44%
202254	X51AG	61%
202232	XH7GY	56%
202227	XH7GY	33%
202229	X51AG	46%
202231	X51AG	22%

 

[Peter_SSs]

However, i do not see any validation against the group id.

Yes, sorry, I missed that completely.

Try again

22 04 14.xlsm
	A	B	C	D	E	F
1	Sheet 1
2	Start date	Start week	End date	End week	Group id	Result
3	11/02/2022	202249	6/03/2022	202252	X51AG	40.0%
4	5/01/2022	202228	18/01/2022	202233	XH7GY	56.0%
5	13/01/2022	202229	22/02/2011	202231	X51AG	34.0%
6
7	Week	Group id	Margin
8	202245	X51AG	56%
9	202249	X51AG	36%
10	202252	X51AG	44%
11	202254	X51AG	61%
12	202232	XH7GY	56%
13	202227	XH7GY	33%
14	202229	X51AG	46%
15	202231	X51AG	22%
Average (2)

Cell Formulas
Range	Formula
F3:F5	F3	=AVERAGEIFS(C$8:C$15,A$8:A$15,">="&B3,A$8:A$15,"<="&D3,B$8:B$15,E3)