Home |
Search |
Today's Posts |
|
#1
![]() |
|||
|
|||
![]()
I'm trying to Pearson Ranks correlation table. This is the formula I'm
using: =PEARSON(Ranks!$B$3:$B$230,Ranks!B$3:B$230) This is placed in cell B3. When I copy the formula over to DT3 the formula correctly adjusts the B in the second part of the formula all the way to DT and still references the values in the B column. But I want to now copy this down to C4, D5, E6 and then over to DT for each so that it correctly makes the correlation table. What is the best way to do this? Thanks for any help you can provide. |
#2
![]() |
|||
|
|||
![]()
On Mon, 17 Oct 2005 14:21:47 +0800, "Zeelotes"
wrote: I'm trying to Pearson Ranks correlation table. This is the formula I'm using: =PEARSON(Ranks!$B$3:$B$230,Ranks!B$3:B$230) This is placed in cell B3. When I copy the formula over to DT3 the formula correctly adjusts the B in the second part of the formula all the way to DT and still references the values in the B column. But I want to now copy this down to C4, D5, E6 and then over to DT for each so that it correctly makes the correlation table. What is the best way to do this? Thanks for any help you can provide. Just take out the $ sign before the row numbers. Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
![]() |
|||
|
|||
![]()
Just take out the $ sign before the row numbers.
Rgds ------------------------------ Doing this will cause the results to not match the succeeding columns. Let me illustrate: C4 must be: =PEARSON(Ranks!$C$3:$C$230,Ranks!C$3:C$230) D5 must be: =PEARSON(Ranks!$D$3:$D$230,Ranks!D$3:D$230) E6 must be: =PEARSON(Ranks!$E$3:$B$230,Ranks!E$3:B$230) In other words, each successive row must be fixed in the first part to the next column over. I'm guessing you have to use COLUMN or INDEX or OFFSET or some similar function which I am not familiar with. Thanks for the offer of help. |
#4
![]() |
|||
|
|||
![]()
Perhaps one way ..
Try in B3: =PEARSON(OFFSET(Ranks!$A$3:$A$10,,ROWS($A$1:A1)),R anks!B$3:B$10) Copy B3 across to DT3, fill down -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Zeelotes" wrote in message ... Just take out the $ sign before the row numbers. Rgds ------------------------------ Doing this will cause the results to not match the succeeding columns. Let me illustrate: C4 must be: =PEARSON(Ranks!$C$3:$C$230,Ranks!C$3:C$230) D5 must be: =PEARSON(Ranks!$D$3:$D$230,Ranks!D$3:D$230) E6 must be: =PEARSON(Ranks!$E$3:$B$230,Ranks!E$3:B$230) In other words, each successive row must be fixed in the first part to the next column over. I'm guessing you have to use COLUMN or INDEX or OFFSET or some similar function which I am not familiar with. Thanks for the offer of help. |
#5
![]() |
|||
|
|||
![]()
Some corrections & refinements to the earlier post ..
Put instead in B3: =PEARSON(OFFSET(Ranks!$A$3:$A$230,,ROWS($A$1:A1)), Ranks!B$3:B$230) (corrections made for cell refs) With B3 selected, Click Format Conditional Formatting (CF) Under condition 1, make the settings as: Formula is: =ROWS($A$1:A1)COLUMNS($A$1:A1) Click Format button Font tab Font Color white OK Click OK at the main dialog Copy B3 across to DT3, fill down The CF will make the diagonal half below "invisible" (assuming the fill color is the default: no color) If you don't want the diagonal 1's to appear as well, just change the CF formula in the starting cell B3 to: =ROWS($A$1:A1)=COLUMNS($A$1:A1) and copy across/fill down as before -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
![]() |
|||
|
|||
![]()
Thanks a million. That works perfectly. Now I just need to work on
understanding it so that I can use it again. -:) |
#7
![]() |
|||
|
|||
![]()
You're welcome !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Zeelotes" wrote in message ... Thanks a million. That works perfectly. Now I just need to work on understanding it so that I can use it again. -:) |
#8
![]() |
|||
|
|||
![]()
On Mon, 17 Oct 2005 18:02:59 +0800, "Zeelotes"
wrote: Just take out the $ sign before the row numbers. Rgds ------------------------------ Doing this will cause the results to not match the succeeding columns. Let me illustrate: C4 must be: =PEARSON(Ranks!$C$3:$C$230,Ranks!C$3:C$230) D5 must be: =PEARSON(Ranks!$D$3:$D$230,Ranks!D$3:D$230) E6 must be: =PEARSON(Ranks!$E$3:$B$230,Ranks!E$3:B$230) In other words, each successive row must be fixed in the first part to the next column over. I'm guessing you have to use COLUMN or INDEX or OFFSET or some similar function which I am not familiar with. Thanks for the offer of help. Slightly puzzled. Is there a typo above? You indicate C4 references column C in both parts, D5 references column D in both parts, but E6 references both columns E & B in both parts. This doesn't seem consistent. The solution to your query no doubt lies with the INDIRECT function but I'm not yet quite understanding your query. Secondary question, doesn't the Rank correlation require two different data sets, one dependent on the other? If so why are both sets the same in your example? Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#9
![]() |
|||
|
|||
![]()
Slightly puzzled. Is there a typo above? -- Yes, a typo to the right of E6.
The solution to your query no doubt lies with the INDIRECT function but I'm not yet quite understanding your query. -- I'm sure willing to learn how if you can explain. Max's solution works perfectly, but if there is another way I'm sure I'll learn even more from this. Secondary question, doesn't the Rank correlation require two different data sets, one dependent on the other? If so why are both sets the same in your example? -- the result of the formula I give is 100% or just one depending on the formatting. But as the formula is copied over to the right, the values will reflect the Spearman rank for each of the distinct trading strategies being compared to the one that is locked. Make sense? |
#10
![]() |
|||
|
|||
![]()
On Mon, 17 Oct 2005 19:13:52 +0800, "Zeelotes"
wrote: Slightly puzzled. Is there a typo above? -- Yes, a typo to the right of E6. With =PEARSON(Ranks!C$3:C$230,Ranks!C$3:C$230) in C3, this will copy to say E6 as =PEARSON(Ranks!E$3:E$230,Ranks!E$3:E$230), which appears to satisfy your original query - or am I still missing something? Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#11
![]() |
|||
|
|||
![]()
It appears that you already have a solution, so my contribution is
simply to point out that if you are using an Excel version prior to 2003, then you should be using CORREL() instead of PEARSON(). CORREL() and PEARSON() are mathematically equivalent, but in versions prior to 2003, PEARSON() uses a numerically inferior algorithm. Jerry Zeelotes wrote: I'm trying to Pearson Ranks correlation table. This is the formula I'm using: =PEARSON(Ranks!$B$3:$B$230,Ranks!B$3:B$230) This is placed in cell B3. When I copy the formula over to DT3 the formula correctly adjusts the B in the second part of the formula all the way to DT and still references the values in the B column. But I want to now copy this down to C4, D5, E6 and then over to DT for each so that it correctly makes the correlation table. What is the best way to do this? Thanks for any help you can provide. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to pull numbers from two tables with conditions? | Excel Worksheet Functions | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) | |||
Automation of Path of linked tables | New Users to Excel | |||
How to change default page setup for pivot tables? | Excel Discussion (Misc queries) | |||
How to paste Xcel tables legibly into Word . Help!! | Excel Discussion (Misc queries) |