Spearman’s Rank Correlation in Numbers

Michaela is doing a biostatistics paper, and today she was working on calculating Spearman’s Rank Correlation. While she was doing this by hand, I set about doing it in Numbers.

The tricky part was the ranking, as the textbook says to give equal ranking items the ‘average of the ranks’ they would have if unique, thus the values {10, 11, 11, 12} have the rank’s {1, 2.5, 2.5, 4}. The RANK function in Numbers gives nth equals ranking (thus giving {1, 2, 2, 4} for the prior data).

To calculate Spearman’s Rank for value A2 from column A use (in C2 for example):


Pulling the formula down for all values in A (giving the ranks of A in column C). Do again for the second of the paired values in B (into column D). You then can calculate d and thus calculate d2 in their own columns and finally get Σd2.  With n in cell H3 and Σd2 in cell H2 you can pop this into a cell:


and get your rs

Looking at the Wikipedia article, it has a single line Excel formula, but the Numbers RANK function is not as complete as Excel’s, so you can’t use it. The formula also uses the CORREL function, but that is a linear regression, not the Spearman’s regression, so it’s not much use here either. Thus it seems you need an ugly table like this it after all:

This all applies to Numbers ’09.

CafePress Fix Checkout with Amazon, or Remove It!

For the last week I have been trying to buy a t-shirt from CafePress for the Scott Kelby Worldwide Photowalk, and CafePress have three options to pay.

Sweet, ‘Checkout with Amazon’ I say, I love Amazon. So press that, then log into Amazon, press ‘Submit my order’ and get an error saying:

You must specify a state of the corrosponding country

Really, it states my state quite clearly to me, in fact, I didn’t enter any values. It even knows the magic long zip code, which means blanking out my house is almost pointless.

State Stated!

So I called the CafePress phone support, and the guy on the other end siad: “Oh yeah that got broken a few weeks ago with an update from Amazon, our IT guys are looking into it. I can complete your order over the phone if you like”

Um, how about if it’s been broken for two weeks, and your IT guys are too clueless to fix it in that time, you edit the page to not show the Amazon option. Then I would have seen your online payment option or Pay Pal, and chosen yours.

But instead you gave me a known broken option, and expected me to chase you as to how to work around the problem.

So now I have a dilemma, I want to have the t-shirt, but I don’t want to support such a useless merchant.

[Update 11 Sep]Still broken 2 ½ weeks later, so it’s been broken for a whole month. Cafe Press need some new coders.

WinCE Remote Debugging: “No process is on the other end of the pipe.”

I have a new WinCE platform to port our project onto, and was getting “No process is on the other end of the pipe” when I would try remote debug the app, after successfully deploying via WinCE development without Active Sync on Win 7 as previously mentioned.

The application would also silently refuse to run when launched from WinCE explorer or the command shell.

I created a new default application, and it remote deployed and ran. So I knew there was something special with my application.

I tweaked lots of things that had zero impact, but in the end adding Wininet.lib and calling InternetOpen caused my test app to also not run. If I included the library as a depenacy but didn’t call InternetOpen the application runs fine, if I call InternetOpen it fails.

I suspect Wininet.dll is not present of the development WinCE 6 OS I have, and therefore the failure to load the .dll is pulling the executable down.

Email sent off to OS team, so I’m back onto a different project…

[Update: A few hours later…] Yip, wininet.dll has been removed.