Stats Excel Spearman Function

.docx Document (11k)

Sign up or sign in to download this file.

Copy URL

Copy URL

The following URL can be copied and pasted into emails or blog posts.

Excel has a built-in statistical function =CORREL(array1,array2), which works great to correlate two sets of data (Pearson correlation). However, it does not provide a solution for the Spearman correlation (between rank of two sets).
This VBA code is an add-in function you can create such that =SPEARMAN(array1,array2) calculates immediately the Spearman correlation without having to go through the intermediate RANK() calculation columns. You can also use this code as a stand-alone macro (change "function" to "sub").