Formula to find the Average using 4 Criteria's which are need to be extracted using isnumber, search & average

[suresh7860]

Hello Experts:

I am looking for a formula that can help me to get the average of the multiple period results for the control id's by using Company Name; Code; Activity.

The only ID will be in the defined format but other columns will not be inconsistent. But Company Name will be same like MNO similar for all the times listed and similarly, Code also likes 105 will be same for all the times it's listed in the report and even the activity X012.03.01 which will be listed similarly for all the times listed in the report.

ID#456
Company Name#MNO
Code#105
Activity#X012.03.01

The result should be an average of the similar combinations of how many times listed in the report and create an average for the same key.

In this example, we have twice and the average result for this is 90. Average of 100 (Jan'21) + 80 (Feb'21) / 2 (listed twice)

ID	Company Name	Code	Activity	Result	Period
123​	ABC|XYZ.|DEF|MNO|QRS	101|108|205|225|171	X001.01.02|X012.02.01|X018.01.05	90​	Jan'21
456​	MNO|XYZ.|RVS	225|229|105|101|201	X001.01.02|X012.03.01|X019.02.03	100​	Jan'21
235​	ABC|QRS	201|205|701	X001.01.02|X012.04.01|X022.05.01|X123.01.05|X027.02.03	85​	Jan'21
456​	QRS|XYZ|MNO|GIJ	105|208|801	X002.03.05|X012.02.03|X012.03.01|X023.04.03	80​	Feb'21
123​	ABC|XYZ.|DEF|MNO	101|108|205|225|171	X001.01.02|X012.02.01|X018.01.05	100​	Feb'21

Thank you

Regards
Suresh

 

[jasonb75]

With messy data come messy formulas.

Book1
	A	B	C	D	E	F
1	ID	Company Name	Code	Activity	Result	Period
2	123	ABC|XYZ.|DEF|MNO|QRS	101|108|205|225|171	X001.01.02|X012.02.01|X018.01.05	90	Jan'21
3	456	MNO|XYZ.|RVS	225|229|105|101|201	X001.01.02|X012.03.01|X019.02.03	100	Jan'21
4	235	ABC|QRS	201|205|701	X001.01.02|X012.04.01|X022.05.01|X123.01.05|X027.02.03	85	Jan'21
5	456	QRS|XYZ|MNO|GIJ	105|208|801	X002.03.05|X012.02.03|X012.03.01|X023.04.03	80	Feb'21
6	123	ABC|XYZ.|DEF|MNO	101|108|205|225|171	X001.01.02|X012.02.01|X018.01.05	100	Feb'21
7
8
9	ID#	456
10	Company Name#	MNO
11	Code#	105
12	Activity#	X012.03.01
13
14	Average	90
Sheet5

Cell Formulas
Range	Formula
B14	B14	=AVERAGE(IF(A2:A6=B9,IF(ISNUMBER(SEARCH("|"&B10&"|","|"&B2:B6&"|")),IF(ISNUMBER(SEARCH("|"&B11&"|","|"&C2:C6&"|")),IF(ISNUMBER(SEARCH("|"&B12&"|","|"&D2:D6&"|")),E2:E6)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[suresh7860]

T

With messy data come messy formulas.

Book1
	A	B	C	D	E	F
1	ID	Company Name	Code	Activity	Result	Period
2	123	ABC|XYZ.|DEF|MNO|QRS	101|108|205|225|171	X001.01.02|X012.02.01|X018.01.05	90	Jan'21
3	456	MNO|XYZ.|RVS	225|229|105|101|201	X001.01.02|X012.03.01|X019.02.03	100	Jan'21
4	235	ABC|QRS	201|205|701	X001.01.02|X012.04.01|X022.05.01|X123.01.05|X027.02.03	85	Jan'21
5	456	QRS|XYZ|MNO|GIJ	105|208|801	X002.03.05|X012.02.03|X012.03.01|X023.04.03	80	Feb'21
6	123	ABC|XYZ.|DEF|MNO	101|108|205|225|171	X001.01.02|X012.02.01|X018.01.05	100	Feb'21
7
8
9	ID#	456
10	Company Name#	MNO
11	Code#	105
12	Activity#	X012.03.01
13
14	Average	90
Sheet5

Cell Formulas
Range	Formula
B14	B14	=AVERAGE(IF(A2:A6=B9,IF(ISNUMBER(SEARCH("|"&B10&"|","|"&B2:B6&"|")),IF(ISNUMBER(SEARCH("|"&B11&"|","|"&C2:C6&"|")),IF(ISNUMBER(SEARCH("|"&B12&"|","|"&D2:D6&"|")),E2:E6)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you Jason, its working with the example and will test the same with the real data and confirm back tomorrow Have a great weekend ahead!!