$50 Project - Parse Google Search Results within a Domain for First Result

I recently completed a $50 Project for a client who wanted a spreadsheet where he could have a list of domains in which to search and a list of search terms and return the URL for the first returned search result from Google while searching withing the specific domain. It took a little modification of some other stuff I've done with parsing Google search pages, but it seems to work nicely.

Excel_Geek Insiders, your file is on it's way.

Later,

Excel_Geek

Chat Support RE: Counting in Pivot Tables

Here's another one from yesterday:

[15:40] meeboguest######: hi excel_geek - looking for a cont function in a pivot table... can you help?
[15:40] excel_geek: cont?
[15:40] excel_geek: count?
[15:40] meeboguest######: oups.. "count"
[15:42] meeboguest######: have multiple entries by various "agents" and wnat to find how many agents are ther in total... regardless of the entries
[15:42] excel_geek: sure
[15:42] excel_geek: right click in the table detail part
[15:42] excel_geek: and pick field options
[15:42] excel_geek: then select "count"
[15:44] excel_geek: follow?
[15:44] meeboguest######: ok
[15:46] meeboguest######: it gives me the total count of entries, not count of unique individuals... (1 individual = multiple entries)...
[15:47] excel_geek: hmmm
[15:47] excel_geek: maybe i should back up
[15:48] excel_geek: so drag the agents field into the left part of the pivot table
[15:48] excel_geek: then drag any other column into the right (detail) part
[15:48] excel_geek: the right click the field you put in the right part and field settings to "count"
[15:50] meeboguest######: ok... i think i figured it out!
[15:50] meeboguest######: thx a bunch :)
[15:50] excel_geek: np
[15:50] excel_geek: thanks for stopping by
[15:50] meeboguest######: :D

10 Minutes to understanding. Not too bad. Some of that pivot table stuff is unintuitive.

Chat Support RE: =INDIRECT() Function

More and more I'm helping folks via the MeeboMe chat window I've embedded in the blog. I thought it'd be interesting for you all to see the sorts of conversations I'm having this way:

[14:36] meeboguest######: eric!
[14:36] meeboguest######: do you have a sec?
[14:36] XLgeke: for u?
[14:36] XLgeke: always
[14:36] XLgeke: and now it's over... ;)
[14:36] XLgeke: what's up
[14:36] meeboguest######: excel question
[14:36] meeboguest######: umm
[14:36] meeboguest######: trying to think how to explain
[14:37] XLgeke: reboot
[14:37] XLgeke: that should do it
[14:37] meeboguest######:
[14:37] meeboguest######: ok, I'm wondering if there's a way to dynamically reference a cell in a formula
[14:37] XLgeke: yes
[14:38] meeboguest######: so that if I was to reference cell B
[14:38] XLgeke: right
[14:38] XLgeke: use =INDIRECT()
[14:38] meeboguest######: and that number is a value generated in another cell
[14:38] XLgeke: so like
[14:38] meeboguest######: oooh
[14:38] XLgeke: =INDIRECT("B"&B1")
[14:38] meeboguest######: woah
[14:39] meeboguest######: nice
[14:39] XLgeke: in B1 you'd change the number for BX
[14:39] meeboguest######: right
[14:39] meeboguest######: I'll give that a shot
[14:39] meeboguest######: thanks!
[14:39] XLgeke: np

And that was it...less than 3 minutes and issue solved. This happens scores of times each month.

Finally - Pool Administrator version of March Madness Spreadsheet is up

UPDATE - 03/27/09 8:52 AM CDT: Another bug found! Ugh! The "master" file was not properly updating the results of last night's games. It's fixed now in this update. (Relatively) good news: it only affected the "master" sheet, so you only have to re-input your picks in the master and retype in the filenames, etc. of the individual sheets in the summary page.

UPDATE - 03/22/09: I found a problem with the files. It's not summarizing the regional semifinals round properly. I've worked out the fix and here are the corrected versions of both the freebie individual file and the "master" file.

It's done. It's late. I've tried to debug as much as I could think to. There's a lot new going on behind the scenes, though, so if you run into trouble, just drop me a note. I'll try to help you out.

Download the file. When you open the "master" file, you'll be given a lock code. Email that to me after you PayPal me $3.00, and I'll get you set up.

Good nite,

Excel_Geek

2009 March Madness Bracket -- Freebie for tracking your own picks

Okay...so I thought I'd get out this before too late tonight.

Here is the free spreadsheet that anyone can use to track his or her own picks versus actual results in the 2009 NCAA basketball tournament.

What I'm still working on is the companion spreadsheet intended for those of us who coordinate the thousands of March Madness office pools. When it's done I'll simply add it into the zipped directory with this file. It'll cost $3.00 to use. If you like you can PayPal me $3.00 now and I'll put you on the list to get the 2009 version as soon as it's ready.

2009 March Madness NCAA Backetball Bracket

The inquiries are piling in now...

Yes. I am doing a new bracket spreadsheet for 2009. I'll probably be working on it again tonight for awhile. You can still find the older versions I've done here:

2007
2008

If you do download an older version, though, please don't ask me to make a bunch of feature changes to it. They're old. I'm working on a new one. If you like you can PayPal me $3.00 now and I'll put you on the list to get the 2009 version as soon as it's ready.

What'll be new in the 2009 version? Well, I'm going to put more focus on those "pool administrators" out there. This version will actually have two companion spreadsheets. The first is for the "pool administrator" where he or she can both set up the points system for each round, track his or her own bracket, as well as those of all the people in there pool. The second will be the bracket file for each of the participants. The "pool administrator" file will be the only one locked down and password protected as it was in prior versions. The other spreadsheet will be available for anyone to download and use.

Oh and if you're an Insiders subscriber, as always, you can get the files for free. Just drop me a note.

More to come...stay tuned...

Excel_Geek

Heat Charts for Crime Stats Analysis

I recently got a request for a $50 Project from Kurt Smith with the San Diego County Sheriff's Department Crime Analysis Unit. (Cool, huh?)

Kurt wanted to apply the heat charting techniques I've posted about before to crime statistics on a Time-of-Day vs. Day-of-Week form. He shot me over some sample data on vehicle thefts. A simple Copy --> Paste Special... --> Values operation later and here's what we've got...



I'm obviously biased, but I think heat charts is a great way to visualize this sort of data, which can tend to get lost in tabular form. In this example, you can easily see the clustering occur just after midnight on the weekends.

The SDSD's apparently got a few Excel geek's on staff, as Kurt tells me that their resident Excel afficianado, Ted, has "...played with it and built some other ranges that approximate standard deviation, 'thirds' and so on from percentiles..." and that they're "...going to begin using it to replace our Excel surface charts for when particular crime types are being reported (we use split times and some aoristic analysis, depending on the crime)..." Whoa...slow down...you're losing me, Kurt.

Kurt also tells me that Lincoln's very own Police Chief Tom Casady is a stats/spreadsheet junky, too. Who knew this? Apparently there's a whole hidden world of Excel geeks: Cops! I'll have to subscribe to Chief Casady's blog.

Excel_Geek Insiders subscribers, I've sent this file out before, but just in case you've lost it, this new version is on it's way.

Later,

Excel_Geek