Finding the Correlation Between Two Arrays, but only if a third column contains a specific

Hello All.

Thanks in advance for any help. I did search for an answer to my question, but did not find an answer.

I'm trying to find the correlation between two arrays. In the example I have created (and attached) I'd like to find the correlation between "hits" (in column B) and the weather (which "sunny" or "cloudy" in column C, but which I have converted to binary, 0 or 1, in column D).

So, I am looking for the correlation between column B and column D. But...

I don't want it for the whole array. In column A, we have the "hitters" names: Amy, Beth, and Carrie. I want to know: what is the correlation between hits (col B) and weather (col d) when the hitter is Amy (col a)? When the hitter is Beth? Carrie?

I appreciate any help with this

Thanks for getting back to me so quickly on this. I'm going to play with this then report back

Hmm, I'm working with this, and I get a div/0 when O ctrl/shft/enter.

even after I change the values of the data. why?

After changing some of the values of weather (the binary one) to 0, I found I started getting some meaningful correlations.

Does this mean that when you have a correlation of 1, excel will sometimes express that as "Div/0"?