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

 

[Exceldeal]

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

You should share some data example too.!

 

[Kkajr]

Okay, im really new to excel and the forum, how do you like to see data?

 

[Kkajr]

Sorry its a little broke, next time itll look a lot cleaner, The B column is just filled with 1s or 0s, I also didnt include a similar picture (because of the 4 pic limit) but i showed the m column function at m20, and the output was 2/9

 

[Fluff]

You might have a better chance of getting some help if you post some actual data, rather than an image, there are some tools here https://www.mrexcel.com/forum/about-board/508133-attachments.html
that will help you do that & you can test them out on the test board https://www.mrexcel.com/forum/test-here/
before posting to the thread

 

[Aladin Akyurek]

The condition that D2:D892 must meet is not clear. Care to clarify?

 

[Kkajr]

Thank you aladin for asking that, now im quickly trying to fix it in a way that makes more sense, but im aware it still wont work, pretty much I want to figure out how likely a person is to survive if they had a sibling who survived
Im writing the averageifs right now, so if baclani had a sibling (g column) I want to find every other baclani in the list and average their B which is survival rate, of either a 0 or a 1. My issue arises when looking up the name because it looks up all names

 

[Kkajr]

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

So this works okay, but It only calculates the average chance of survival of a person based on people with the same last name, in 1 row. I want the total average of all the averages of all the rows of people who might be siblings. Also im working on a small fix because my condition includes anyone who has that name, not just last name.

 

[Aladin Akyurek]

You are not answering the question poste in #6 .

"*"&LEFT(D2:D892,(FIND(",",D2:D892,1)-1))&"*" means everybody listed in

D2:D892​

, not a single individual or subset of individuals where the subset is smaller than the whole set.

By the way, it's not helpful posting images as it would would be hard to find someone who would retype into Excel what the mage shows in order to help.
​

 

[Kkajr]

Yeah i was meaning to go figure out how to post the code, ill do that in a second here. In answer to #6 , what i meant by D2:d829, was I wanted it to search the name at a given cell, so like when its at d2, itll look at the name in d2, when its at d3, itll look at the name in d3... etc. and look through the full list for each.