Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Zeelotes
 
Posts: n/a
Default Correlation Tables -- Setup

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   Report Post  
Richard Buttrey
 
Posts: n/a
Default Correlation Tables -- Setup

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   Report Post  
Zeelotes
 
Posts: n/a
Default Correlation Tables -- Setup

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   Report Post  
Max
 
Posts: n/a
Default Correlation Tables -- Setup

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   Report Post  
Max
 
Posts: n/a
Default Correlation Tables -- Setup

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   Report Post  
Zeelotes
 
Posts: n/a
Default Correlation Tables -- Setup

Thanks a million. That works perfectly. Now I just need to work on
understanding it so that I can use it again. -:)


  #7   Report Post  
Richard Buttrey
 
Posts: n/a
Default Correlation Tables -- Setup

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
__________________________
  #8   Report Post  
Zeelotes
 
Posts: n/a
Default Correlation Tables -- Setup

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?


  #9   Report Post  
Richard Buttrey
 
Posts: n/a
Default Correlation Tables -- Setup

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
__________________________
  #10   Report Post  
Zeelotes
 
Posts: n/a
Default Correlation Tables -- Setup

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?

Let me illustrate with an example:

D3 should be: =PEARSON(Ranks!$C$3:$C$230,Ranks!D$3:D$230)

But per your suggested formula it would be:
=PEARSON(Ranks!D$3:D$230,Ranks!D$3:D$230)

As a result, all of the results will always reference itself and result in a
result of 100%.




  #11   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default Correlation Tables -- Setup

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.


  #12   Report Post  
Max
 
Posts: n/a
Default Correlation Tables -- Setup

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. -:)




  #13   Report Post  
 
Posts: n/a
Default Correlation Tables -- Setup

Be aware of the following:
1. PEARSON() do not calculate correctly in Excel 2000.
- Use CORREL()

In versions of that are Excel earlier than Excel 2003, PEARSON may
exhibit round-off errors. The behavior of PEARSON has been improved in
Excel 2003. CORREL has always been implemented with the improved
procedure that is now used in Excel 2003. Therefore, if you are using
PEARSON for a version of Excel that is earlier than Excel 2003,
Microsoft recommends that you use CORREL instead."
http://support.microsoft.com/default...product=xl2003
(Why do microsoft implement two different version of the same
function?)

"In versions of Excel that are earlier than Excel 2003, PEARSON may
exhibit round-off errors. This behavior leads to round-off errors in
RSQ"
http://support.microsoft.com/default...product=xl2003



2. RANK()
- If you have tieded ranks you need to use a formula like (rank data
in B6:B17)

=RANK($B6,$B$6:$B$17,1) + 0.5*(COUNTIF($B$6:$B$17,"=" & $B6)-1)

or this by microsoft

=RANK(B6,$B$6:$B$17,1) + (COUNT($B$6:$B$17) + 1 -
RANK($B6,$B$6:$B$17,0) - RANK($B6,$B$6:$B$17,1))/2

Excel Statistical Functions: Representing Ties by Using RANK
http://support.microsoft.com/default...product=xl2003


Regards
Nikolai
http://www.pvv.org/~nsaa/excel.html

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to pull numbers from two tables with conditions? Anna Excel Worksheet Functions 1 May 14th 05 03:21 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 02:03 AM
Automation of Path of linked tables S Patel New Users to Excel 1 February 10th 05 06:53 PM
How to change default page setup for pivot tables? bobs Excel Discussion (Misc queries) 1 January 5th 05 10:42 PM
How to paste Xcel tables legibly into Word . Help!! Nimo Excel Discussion (Misc queries) 1 December 14th 04 10:53 PM


All times are GMT +1. The time now is 05:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"