correlation with an IF statement?

[ghard1]

Hi,
I'm trying to calculate the correlation for an array of values when a condition is true. For example, I would like to find the correlation of two columns of data (column B and Column C) IF Column A = Apple. I would then like to do the same when Column A = Pear.

Is there an easy way to do this?

Many thanks!

 

[Aladin Akyurek]

That makes sense to me logically, but I am still getting a #NAME? ERROR.

I have a worksheet (tab) called "Player" where I am trying to write this formula. Using this example, in A2 I have "apple", in A3 I have "pear", etc. I have a master data file that I have called "Model"

In this example I am trying to correlate two sets of data from the master file "Model". The first set of data that I want to correlate is in column H, and the second set of data is in column BR. Column A is where the different "fruit" (apple, pear, etc.) are labeled. Each fruit has around 20 different rows of data that are scattered throughout the "Model" sheet.

This is what I have tried:

=CORREL(IF(INDIRECT("'"&Model&"'!A:A")=A3,INDIRECT("'"&Model&"'!H:H")),INDIRECT("'"&Model&"'!BR:BR"))

1. Worksheet
2. That makes sense. I will adjust.

Thanks again!

We are in Player.

We have Apple in A2 of Player.

In B2 of Player control+shift+enter, not just enter, and copy down:

=CORREL(IF(Model!$A$2:$A$800=A2,Model!$H$2:$H$800),Model!$BR$2:$BR$800)

INDIRECT is not needed if you have nothing but the Model sheet.

 

[moore2102]

That was it!!

Thanks you so much!

 

[Aladin Akyurek]

That was it!!

Thanks you so much!

You are welcome.

 

[moore2102]

We are in Player.

We have Apple in A2 of Player.

In B2 of Player control+shift+enter, not just enter, and copy down:

=CORREL(IF(Model!$A$2:$A$800=A2,Model!$H$2:$H$800),Model!$BR$2:$BR$800)

INDIRECT is not needed if you have nothing but the Model sheet.

In this example, do you know how i would set up the formula to find the correlation for A2, and A3, and A4, etc.?

Thanks

 

[Aladin Akyurek]

In this example, do you know how i would set up the formula to find the correlation for A2, and A3, and A4, etc.?

Thanks

Are we not just copying down the formula that is in B2? When done so, the formula calculates a correlation for A3, A4, etc.