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.
I’m increasingly enjoying my sadly flickering i5 iMac. The performance is great, and when the screen doesn’t flicker it’s fairly agreeable.
I’m not sure I’d buy it again though – especially if Apple were to produce an i5 in a smaller form factor.
The screen is just too high. On a conventional table and chair I spend too much time looking up. A sore neck awaits.
I could move the display much further away, but then I’d be unable to read the screen.
It’s the wrong computing form factor for human anatomy. I’d like the top of the screen to be about 8-10” lower, and then grow the display area horizontally. Practically speaking the classic dual monitor arrangement would work better.
Of course that aspect ratio isn’t nearly as good for watching a movie, which mostly shows that one screen won’t work for movies and productivity.
The iMac does drive two displays of course. I think I may end up using an older 21” Dell LCD as my primary reading area, and the massive 27” screen as my photo, video and general workspace. This will take some desk manipulation …
Update 12/17/09: I'm doing better by learning to create smaller windows, and move them to the bottom of the screen. So the top half is a parking area, and the bottom half a working area. It helps when apps open new windows near old ones - Safari does this well. I'm looking forward to the connection to my external Dell however.
Update 12/22/09: I got my mini-DisplayPort to DVI cable, and now I have my Dell 2007WFP as an secondary display. It's a bit tricky to adjust the two to matching brightness levels, I found it easiest to pick a mid-range level for the Dell then use the keyboard control to adjust the i5. (Note the old iMac ambient light sensor is gone!). This arrangement is easy on my eyes, especially since I boosted my Safari font size to suit the high pixel density of the i5. It's quite a display surface, even if Emily does remind me that Al Gore's displays are much bigger.
Update 12/29/09: The enormous Apple discussion thread on this topic includes posts from customers developing flicker problems weeks after buying an i5. I decided to go ahead and apply the firmware update in case a firmware problem might be causing overheating and damage to the GPU. Of course it helps that it's about 8F outside, and fairly cool in the computer room.
I’m a huge fan of the potential of Google Reader/Social – especially as Google’s miraculous translation tools improve. I’d love to trace the “like” links to Chinese and Indian annotators, then follow their Reader shares into language and knowledge domains I can’t currently follow. I know that’s only a few months away – even though yesterday’s effort to follow one Chinese geek failed without a trace.
As of today, however, Google Reader/Social is horked.
Exhibit A is taken from a mash-up of Google Reader “notes” and “comments” on a (micro-blog, think Twitter status post) “note” written by Patrick J with comments by Rahul and me …
Google Reader - Patrick J note
… Shared by Hanna... Tried to comment on hers but couldn't.
… I've noticed this too. I've been working on this on and off for a while, here and there. It appears to be more prevalent when both people in reader have protected their items…
.. I also add google reader people to a group that allows them access to my items, and in addition I also share my items with other groups/people in my contacts list. I have not noticed a difference in group membership vs. the `bump'…
… People keep bumping off my groups … I cannot comment on some posts.…
… why cannot they merge same post shared by different people. I mean, let me see the post just once and say these people like it. These people have shared it. You want to comment to someone specific then click on their name else write a general comment and we will share it everywhere among your contacts…
… It's a wreck. The comment/note dichotomy, the failure to merge items shared by multiple people, and the inexplicable following failures….
--
