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.

## No comments:

## Post a Comment