Not able to define Averageifs condition using isnumber search

Question

I am trying to find a formula which can help me find the average of the results for the 4 criteria's used with ID; Company Name; Code & Activity by using isnumber search options: Example: Table with the details coming from the report extracted and format will not be same always:

id	Company Name	Code	Activity	Results
123	ABC	XYZ.	DEF	MNO QRS	X039.05.01X055.01.01
456	MNO	XYZ.	RVS	105-Tax 108-FRA225-REC	X019.01.01	75
235	ABC	QRS	704-BREAK	X001.01.01	90
456	QRS	XYZMNO GIJ	105-Tax201-Fraud 114-QURK	X001.01.01	75

Criteria:

ID |Company Name|Code|Activity  | Result
456| MNO        | 105|X011.02.03| 75 avg of 75 75 = Average of rows 2 & 4 which has these details listed by using isnumber and average combinations

Table can have thousands of rows and format is not fixed hence looking any dynamic formula using the 4 criteria's search from the respective columns to get the average results for the results found.

Its same company data which is combined manually for year to information hence same criteria's available multiple times and we are trying to show the average results for these criteria's

Any formula or macro which can help would be great help as this is my monthly task and taking too much of time to just find the average results

Thanks for your time

Regards Suresh

Your "story" only confuses us, no offence... Why should we know the structure you presented? Can you explain your need in less words, but chosen in a way to make us understand your problem? Something like: I am trying to apply the next formula: "=...." to be applied on some columns, never in the same position, but having the same headers ("H1", "H2", "H3"). The used formula return an error (what error), or does not return what I need, because we need .... and it returns .... Without a reproductible case, nobody can help you, I am afraid. — FaneDuru, Jun 04 '21 at 09:10
Do you have multiple columns of company names? or a single column with a pipe divided string in each cell? — Ron Rosenfeld, Jun 04 '21 at 09:21
Its a single column for each header but multiple company names are listed in one — suresh7860, Jun 04 '21 at 09:37
What do you mean by "but multiple company names are listed in one"? Is the answer to Ron Rosenfeld's question? Are they separated by pipe character? If yes, why do not show to us such an example? If not, what does it mean? Then, how do you want calculating that 'average'? You should choose an example able to show us where from the data used for average are collected. Average from rows 2 and 4 looks to be done per ID, not per name. Is it correct? If yes, why to also take in consideration 'Company Name'? Your question is still not clear enough, I am afraid. Or I am more tired than it should... — FaneDuru, Jun 04 '21 at 10:02
yes correct its for Ron's question. Yes mostly it is separated by pipes otherwise without that also but why its not reflecting here not sure. Actually i have 4 critiera's ID; Company Name; Code & Activity to find the average Result. Id Company Name Code Activity Rating 123 ABC|XYZ.|DEF|MNO|QRS 101|108|205|225|171 X001.01.02|X012.02.01|X018.01.05 90 456 MNO|XYZ.|RVS 225|229|105|101|201 X001.01.02|X012.03.01|X019.02.03 100 235 ABC|QRS 201|205|701 X001.01.02|X012.04.01|X022.05.01|X123.01.05|X027.02.03 75 456 QRS|XYZ|MNO|GIJ 105|208|801 X002.03.05|X012.02.03|X012.03.01|X023.04.03 50 — suresh7860, Jun 04 '21 at 10:14
Can't you just use wildcards for this? `=AVERAGEIFS(data_col,id_col,id_cell,Company_col,"*"&company&"*",Code_col,"*"&code&"*",Activity_col,"*"&activity&"*")` for example — Rory, Jun 10 '21 at 08:52

score 1 · Accepted Answer · answered Jun 04 '21 at 15:38

Assumptions:

User is required to enter/select a certain combination of the four 'criteria'
Average is taken over scores for which all four of these criteria are satisfied (if fewer, score does not 'qualify' to be 'averaged')

Approach

Each of your criteria can be evaluated in the form of a matrix of 1s & 0s corresponding to 'criteria satisfied' and 'not satisfied' resp. Screenshot refers:

=IFERROR(SEARCH("*"&B15:E15&"*",B5:E8),)

Depiction in Excel - grey box

Averages now need to be taken over scores that correspond to a row of '1s' (grey-shaded box). For instance, the first row (0,0,0,1) corresponds to a score of 90; this row includes at least one '0', so 90 is ignored. The second row includes a complete row of four 1s, so 100 is counted 'in' for the "average" calculation... and so on.

Solution

I could not think of an efficient/parsimonious way to combine these calculations. As such, the more criteria you require, the longer the version of a 'single-cell' equation becomes. I'm sure there is a much quicker way, hopefully this soln. will inspire some creative thinking in this regard ☺

Fortunately, whilst its long, it relatively straightforward due to 'necessary' duplication of equations:

1] The average needs to be calculated the good-old fashioned way, using a sum in the numerator and a count in the denominator (otherwise, with this construct, using a simple 'average' equation will tally / average any '0s' which we don't want). Thus, the denominator is virtually identical to the numerator.

2] As mentioned, there are four criteria, the numerator comprises four similar arrays based upon the first formula above

=SUM(($F$5:$F$8*IFERROR(ISNUMBER(SEARCH("*"&E15&"*",$E$5:$E$8)),)*IFERROR(ISNUMBER(SEARCH("*"&D15&"*",$D$5:$D$8)),)*IFERROR(ISNUMBER(SEARCH("*"&C15&"*",$C$5:$C$8)),)*IFERROR(ISNUMBER(SEARCH("*"&C15&"*",$C$5:$C$8)),)*(IFERROR(ISNUMBER(SEARCH("*"&B15&"*",B$5:B$8)),))*IFERROR(ISNUMBER(SEARCH("*"&B15&"*",B$5:B$8)),)))/ SUM(1*((IFERROR(ISNUMBER(SEARCH("*"&E15&"*",$E$5:$E$8)),)*IFERROR(ISNUMBER(SEARCH("*"&D15&"*",$D$5:$D$8)),)*IFERROR(ISNUMBER(SEARCH("*"&C15&"*",$C$5:$C$8)),)*IFERROR(ISNUMBER(SEARCH("*"&C15&"*",$C$5:$C$8)),)*(IFERROR(ISNUMBER(SEARCH("*"&B15&"*",$B$5:$B$8)),))*IFERROR(ISNUMBER(SEARCH("*"&B15&"*",B$5:$B$8)),))=1))

Google sheets, here. Examples of scenarios 1 & 2 centrefold.

Thanks a lot, James it's working well with the example and will try to test the same with the original data too and it should work I believe. But will confirm back tomorrow. Have a great weekend ahead — suresh7860, Jun 04 '21 at 16:21

Not able to define Averageifs condition using isnumber search

1 Answers1