Complication Correlation Formula

C

currybai

Board Regular
Joined
Jul 14, 2008
Messages
202
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ID</td><td style=";">Date</td><td style=";">A%</td><td style=";">B%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ABC</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">1%</td><td style="text-align: right;;">7%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">ABC</td><td style="text-align: right;;">1/2/2011</td><td style="text-align: right;;">2%</td><td style="text-align: right;;">6%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Correlation</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">ABC</td><td style="text-align: right;;">1/3/2011</td><td style="text-align: right;;">3%</td><td style="text-align: right;;">5%</td><td style="text-align: right;;"></td><td style=";">ABC</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">ABC</td><td style="text-align: right;;">1/4/2011</td><td style="text-align: right;;">4%</td><td style="text-align: right;;">4%</td><td style="text-align: right;;"></td><td style=";">BAC</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">ABC</td><td style="text-align: right;;">1/5/2011</td><td style="text-align: right;;">5%</td><td style="text-align: right;;">3%</td><td style="text-align: right;;"></td><td style=";">DFG</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">ABC</td><td style="text-align: right;;">1/6/2011</td><td style="text-align: right;;">6%</td><td style="text-align: right;;">2%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">ABC</td><td style="text-align: right;;">1/7/2011</td><td style="text-align: right;;">7%</td><td style="text-align: right;;">1%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">BAC</td><td style="text-align: right;;">1/4/2011</td><td style="text-align: right;;">3%</td><td style="text-align: right;;">3%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">BAC</td><td style="text-align: right;;">1/5/2011</td><td style="text-align: right;;">4%</td><td style="text-align: right;;">2%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">BAC</td><td style="text-align: right;;">1/6/2011</td><td style="text-align: right;;">5%</td><td style="text-align: right;;">1%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">BAC</td><td style="text-align: right;;">1/7/2011</td><td style="text-align: right;;">6%</td><td style="text-align: right;;">3%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">DFG</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">7%</td><td style="text-align: right;;">2%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">DFG</td><td style="text-align: right;;">1/2/2011</td><td style="text-align: right;;">7%</td><td style="text-align: right;;">1%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">DFG</td><td style="text-align: right;;">1/3/2011</td><td style="text-align: right;;">3%</td><td style="text-align: right;;">7%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">DFG</td><td style="text-align: right;;">1/4/2011</td><td style="text-align: right;;">5%</td><td style="text-align: right;;">6%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br />

I was wondering if I can find the correlation of any given ID by looking up the ID name. So for ID ABC, I would want the correlation of A% and B% from 1/1 to 1/7. The date range varies from ID to ID.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
currybai said:
Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="">ID</td><td style="">Date</td><td style="">A%</td><td style="">B%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="">ABC</td><td style="text-align: right;">1/1/2011</td><td style="text-align: right;">1%</td><td style="text-align: right;">7%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="">ABC</td><td style="text-align: right;">1/2/2011</td><td style="text-align: right;">2%</td><td style="text-align: right;">6%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="">Correlation</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="">ABC</td><td style="text-align: right;">1/3/2011</td><td style="text-align: right;">3%</td><td style="text-align: right;">5%</td><td style="text-align: right;">
</td><td style="">ABC</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="">ABC</td><td style="text-align: right;">1/4/2011</td><td style="text-align: right;">4%</td><td style="text-align: right;">4%</td><td style="text-align: right;">
</td><td style="">BAC</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="">ABC</td><td style="text-align: right;">1/5/2011</td><td style="text-align: right;">5%</td><td style="text-align: right;">3%</td><td style="text-align: right;">
</td><td style="">DFG</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="">ABC</td><td style="text-align: right;">1/6/2011</td><td style="text-align: right;">6%</td><td style="text-align: right;">2%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="">ABC</td><td style="text-align: right;">1/7/2011</td><td style="text-align: right;">7%</td><td style="text-align: right;">1%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="">BAC</td><td style="text-align: right;">1/4/2011</td><td style="text-align: right;">3%</td><td style="text-align: right;">3%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="">BAC</td><td style="text-align: right;">1/5/2011</td><td style="text-align: right;">4%</td><td style="text-align: right;">2%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">11</td><td style="">BAC</td><td style="text-align: right;">1/6/2011</td><td style="text-align: right;">5%</td><td style="text-align: right;">1%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">12</td><td style="">BAC</td><td style="text-align: right;">1/7/2011</td><td style="text-align: right;">6%</td><td style="text-align: right;">3%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">13</td><td style="">DFG</td><td style="text-align: right;">1/1/2011</td><td style="text-align: right;">7%</td><td style="text-align: right;">2%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">14</td><td style="">DFG</td><td style="text-align: right;">1/2/2011</td><td style="text-align: right;">7%</td><td style="text-align: right;">1%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">15</td><td style="">DFG</td><td style="text-align: right;">1/3/2011</td><td style="text-align: right;">3%</td><td style="text-align: right;">7%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">16</td><td style="">DFG</td><td style="text-align: right;">1/4/2011</td><td style="text-align: right;">5%</td><td style="text-align: right;">6%</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr></tbody></table>
Sheet3​




I was wondering if I can find the correlation of any given ID by looking up the ID name. So for ID ABC, I would want the correlation of A% and B% from 1/1 to 1/7. The date range varies from ID to ID.
Click to expand...


Bump. Please help.

Thanks
 
Upvote 0
Try:

G4=CORREL(IF($A$2:$A$16=F4,$C$2:$C$16),IF($A$2:$A$16=F4,$D$2:$D$16))

Note that this is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }s around your formula in the formula bar.

Then, copy G4 down.
 
Upvote 0
G1:G2 houses:

<TABLE style="WIDTH: 70pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=93><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3299" width=93><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 70pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=93>1/1/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 70pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=93>1/7/2011</TD></TR></TBODY></TABLE>

G4, control+shift+enter, not just enter, and copy down:
Code: 
=CORREL(IF($A$2:$A$16=F4,IF($B$2:$B$16>=$G$1,
    IF($B$2:$B$16<=$G$2,$C$2:$C$16))),$D$2:$D$16)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
616
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
N
  • Question Question
Data Transformation using VBA like subtotal
Replies
1
Views
146
Domenic
D
E
  • Solved
If formula
Replies
3
Views
61
Peter_SSs
Peter_SSs
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
224
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,509
Messages
6,179,192
Members
452,893
Latest member
denay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top