locked
Rank (Spearman) Correlation of Non-Adjacent Values in Excel in Single Formula RRS feed

  • Question

  • Rank (Spearman) Correlation of Non-Adjacent Values in Excel in Single Formula<o:p></o:p>

    <o:p> </o:p>

    1.            In a single formula, I am trying to get the Rank (Spearman) correlation between two arrays.  One of the arrays contains non-adjacent values.  I can get the Pearson correlation with this formula:<o:p></o:p>

    <o:p> </o:p>

                         =CORREL(CHOOSE({1;2;3},U7,AM7,BE7),HR1:HT1)<o:p></o:p>

    <o:p> </o:p>

    2.            The formula in #1 works because CHOOSE creates an array of the 3 non-adjacent cells and effectively makes them appear ‘adjacent’ to each other.<o:p></o:p>

    <o:p> </o:p>

    3.            The Rank correlation is just the correlation of the ranks, and to get the Rank correlation, I just need to convert the values in cells U7, AM7, and BE7 to ranks. (The values in HR1:HT1 are already ranks.)<o:p></o:p>

    <o:p> </o:p>

    4.            If the non-adjacent values in U7, AM7, and BE7 were adjacent to each other (e.g., in cells U7:U9), I could convert the values to their respective ranks and get the Rank correlation like this:<o:p></o:p>

    <o:p> </o:p>

                         =CORREL(RANK.AVG(U7:U9,U7:U9),HR1:HT1)<o:p></o:p>

    <o:p> </o:p>

    5.            Consequently, I thought I could simply substitute the adjacent values created by “CHOOSE({1;2;3},U7,AM7,BE7)” in place of the naturally occurring adjacent values in (U7:U9) in the formula in #4 to get the Rank correlation, something like this:<o:p></o:p>

    <o:p> </o:p>

                  =CORREL(RANK.AVG(CHOOSE({1;2;3},U7,AM7,BE7),CHOOSE({1;2;3},U7,AM7,BE7)),HR1:HT1)<o:p></o:p>

    <o:p> </o:p>

    Unfortunately, this doesn’t work, either as a regular formula or an array formula.   Nor do these ‘unions’ work:<o:p></o:p>

    <o:p> </o:p>

                  =CORREL(CHOOSE({1;2;3},RANK((U7,AM7,BE7),(U7,AM7,BE7))),HR1:HT1)<o:p></o:p>

    <o:p> </o:p>

                  =CORREL(RANK.AVG((U7,AM7,BE7),(U7,AM7,BE7)),HR1:HT1)<o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    I could get the answer by adding 10 more columns to my spreadsheet, but I don't want to do that.  I need to get the Rank Correlation in one formula.<o:p></o:p>

    <o:p> </o:p>

    Can anyone figure out how to write such a formula?<o:p></o:p>

    <o:p> </o:p>

    Thanks for your help.<o:p></o:p>

    Thursday, April 30, 2020 1:09 PM