Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(.

[Guest]

I get nonsensical results for =CORREL($E43:$E61,H43:H61) where columns E and
H are of the type =RANK(G58,G$43:G$61,1). Does this combination work
correctly?
Both CORREL and RANK are working with simple varables.

Thanks

 

[Jerry W. Lewis]

You need to provide more information.

Jerry

 

[Guest]

You need to provide more information.

Jerry

The entries in column E for rows 43 through 61 are of the type RANK
(G58,G$43:G$61,1) where G58 is the entry for row 58. It gives the order that
the entry in the row has in relation to the entry in the other rows for that
column.

Similarly for column H, where the entries give the order of the values in
each row in yet another column.

And then Correl computes the correlation factor between the values in rows
43 through 61 for columns E and H.

So it's like nesting - although the functions are not the same. Functions
of functions, CORREL(RANK). What is the correlation factor (CORREL) between
two columns whose entries are derived from the order (RANK) of yet other
columns.

I can't get any more explicit than that. RANK works on its own. CORREL
works on its own. CORREL(RANK) gives incorrect results.

 

[Jerry W. Lewis]

=CORREL(E43:E61,H43:H61) will depend only on the values in E43:E61 and
H43:H61, not on the formulas that produced those values. However, you
have not disclosed those values, the value that CORREL returns, or why
you think that CORREL's answer is wrong.

You cryptically refer to nested functions. If you are trying to do
everything in a single formula, instead of referring to the intermediate
results in E43:E61 and H43:H61, then you would use a formula like
=CORREL(RANK(G43:G61,G43:G61,1),RANK(J43:J61,J43:J61,1))

Jerry