I'm using the Spearman correlation coefficient test for multiple non-parametric ranked categories in excel. I used the built in rank function in excel to calculate the rank of each individuals but i have many ties because there are 10 to 208 categories for 2k instances. In the variable with 10 categories excel ranks go directly from 1 to 2100 with no intermediary ranks. Should it be doing this?
The spearmens test is giving high r values like .25 for 2747 instances and very low p values for the two ranks .25 but pearsons on the two ranks gives negligible r values like .05. This is the formula I followed: http://blog.excelmasterseries.com/2010/09/spearman-correlation-coefficient-test.html