Averageif help

[Kkajr]

Hi, so im trying to do an averageif based on if the range contains a certain word, here is how I tried it

=AVERAGEIF(D2:D892, "*"&LEFT(D2:D892,(FIND(",",D2:D892,1)-1))&"*", B2:B892)

But the word it picks is the word on the same row in column D, so if i move the cell up to 5 for example, it would pick the word from d5, move it down to 10, d10.

Any help appreciated, ill have a lot more questions in the coming hours

 

[Aladin Akyurek]

Guessing from the bits and your non-working formula...

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(SEARCH("|"&X2&",","|"&$D$2:&D&892&",")),IF($G$2:$G$892,$B$2:$B$892)))

where X2 is a name of interest like Andersson.
​

​

​

<strike></strike><strike></strike>

 

[Kkajr]

Yeah, so im still working that out, but its the name of interest that is what im stuck on. I want to find out each name at each row separately. Maybe a loop might work, id have to figure that out. And thank you for your patience, I know this might be getting old. Is there a way to (in the criteria) search for any word from every line, but not any word from all lines? Like, at d2 or something I want to look in every row for abbing, and at d5 every row for abelson... etc. But not abbing for all rows, because then thats the only output I get. If that still isnt clear, i'm thinking of a better way to state that.

 

[Kkajr]

Okay, Im gonna write a quick loop to see if that helps.

for(int i; i <= 892; i++) {

=AVERAGEIFS(B2:B892, D2:D892, "*"&LEFT(Di,(FIND(",",Di,1)-1))&"*", G2:G892, "=1")
//where Di is the column D at given row i
}

and I want to average all those outputs.

 

[Aladin Akyurek]

See post #11

 

[Kkajr]

I dont really understand it, i see it takes the average if it looks up name of interest and it finds it, itll return true, taking us to the second if statement which looks like 2 bounds to me, i understand the second bound is the bound the average is gonna use, but the first one doesnt look like a condition.

 

[Aladin Akyurek]

I dont really understand it, i see it takes the average if it looks up name of interest and it finds it, itll return true, taking us to the second if statement which looks like 2 bounds to me, i understand the second bound is the bound the average is gonna use, but the first one doesnt look like a condition.

Did it work for you?

If not, try to post a scaled-down sample (not an image) along with the expected results for that sample?