Using Excel matrix operations to sum a range of inverted numbers

There’s an interesting story here about how Google makes us smarter, but I’ll try to post that one to Gordon’s Notes. This post is about sharing what I learned about Excel.

As we all know Excel is the gem of Microsoft. Word was once great, but it fell (though Word in Office:Mac 2008 is surprisingly good). Excel, which started on the Mac, has always been impressive. This time I used one of its more obscure features to solve a problem of my own creation.

The problem was that I’d asked team members to rank their top three topics in a list of about 40. So their top choice was numbered 1, 2nd choice 2, etc. I knew I’d have trouble interpreting the results, but I wanted to make the data entry process very simple.

When it came to creating a cross-topic metric I ran into the usual troubles. I couldn’t just sum them up. I’m sure there are better solutions, but I decided to sum up the inverted numbers. So if 3 people had rated a topic 1, 2 and 3 then the sum would be 1/1 + 1/2 + 1/3 multiplied by scaling factor to give a more readable result.

Thanks to Google (Google Suggest is mind blowing) I learned that summing the inverse of the non-zero (null) values in a row or column is a matrix operation (I have vague memories that there’s a mathematical name for this value), and that you can do this in Excel (credit to the hideous Experts Exchange for the key entry).

It’s a bit bizarre, but here’s what the formula looks like:

={SUM(IF(ISERROR(1/E41:T41),0,1/E41:T41))*10}

Okay, more or less looks like – because you type it in like this:

SUM(IF(ISERROR(1/E41:T41),0,1/E41:T41))*10

Then you hit Ctrl-Shift-Enter to tell Excel to treat this formula as a matrix operation.

You need the “ISERROR” function so Excel ignores the divide-by-zero (null) cells. The “E41:T41” says that the range goes from column E to T on row 41.

This formula did the job. I’d never have come up with this fix if not for Google, but that’s a topic for another post.