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.
