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.

WinCE development without Active Sync on Win 7

At the start of the year I was given a device to do trial Windows CE development against. I was also given a zip file with the SDK and some instructions.

Step 1: Install Active Sync 4.5 on your Windows XP machine (this will not work on Vista or Win 7)

So my Win 7 lappy is shafted. Luckily some work colleagues had been doing WinCE development ‘differently’ on an earlier product, and they mentioned using ‘CMaccept.exe’.

Search for ‘CMaccept.exe’ leads you to the advice you want: How to: Connect to Windows CE Device Without ActiveSync

Luckily for me my device has working Ethernet, so this worked nicely.

I mention this because today another engineer asked me how I got Active Sync working on the device, as he’s having problems under XP, thus I suggested the above method.

Post VS2010 SP1 Updates Issue

Since installing the Service Pack 1 for Visual Studio 2010, my .sln files for Visual Studio 2005 have not been opening.

This turns out to be, because I had VS2005 set to need Admin Privileges to run, to allow some remote debugging. But I also needed to set this for the Visual Studio Launcher, as this is what chooses which Visual Studio to open the solution file. In applying the service pack, it appears the need Admin Privileges setting was lost.

To test this, I found the VS2005 executable and remove the Admin Privileges requirement, and then my 2005 solutions opened.

But I couldn’t find where the Launcher was, so searching registry for .sln I found the file uses:


looking for this you find it at:


which for my machine points to:

C:\Program Files (x86)\Common Files\Microsoft Shared\MSEnv\VSLauncher.exe

Now knowing the Launchers name/location I can set needs Admin Privileges and everything works as it was.

iTunes Podcasting changes

The new UI change in iTunes had me tripped up, as the download button has been removed from non-downloaded podcast. Thus if you have frequent updating podcasts, and there is a gap, under the old system you could click the download button.

The red arrow shows where the button used to be.

There is now per podcast subscription settings, but the “download all” option which will fill those gaps, will also delete each podcast as it’s listened to.

Well today, I discovered you can just double click on the missing podcast, and it auto downloads.

A very simple UI change, it makes it cleaner, and still allows users like me to do what you want, but very non-discoverable, unless you have the habit of double clicking everything…

[Update: One day later] The new release has the buttons back.

WinCE SDK problems in Windows 7

For the last few weeks, my work project has not been building it’s WinCE target on my new Windows 7 PC. Each project would just say “skipping build”,

12>------ Skipped Build: Project: PROJECT_NAME_12 ------
13>------ Skipped Build: Project: PROJECT_NAME_13 ------
========== Build: 0 succeeded or up-to-date, 0 failed, 13 skipped ==========

and if I tried to edit the project properties (or build/clean/rebuild project only), I would get

The operation could not be completed

It then dawned on my to try a new project test, and the SDK was missing from the options:

A bit of focused searching found Windows CE: SDK Doesn’t Show up in Visual Studio 2008 post, and the solution worked for me with my Visual Studio 2005 SDK under Windows 7 also:

  1. Make sure Visual Studio 2005is not running. I also shut down the device emulator manager but you may not be using that
  2. Open a “Visual Studio 2005 Command Prompt” as Administrator. On Windows 7 just right click the short cut and pick the “Run as administrator” option
  3. Enter the following command: msiexec /package <the path to your .msi file>
  4. Install

You need to used the same steps above to uninstall the SDK as well (as compared to the Control Panel’s ‘Programs and Features’ options), otherwise it won’t uninstall correctly.