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

Bullseye Chart in Excel

The other day I got a $50 Project request from a guy who was analyzing shooting range results -- that's right, guns and stuff. Cool. Anyway...he couldn't figure out a nice, clean way to visually represent the results in scatter plot fashion. He had even started to mess around with various in-cell charting techniques to pull it off.

I decided that Excel's built-in XY scatter plot graph should be able to do the trick, but it took treating each data point as it's own series (so you can use the "shot number" as the data series name and place it in the data point), adding additional data series to "draw" the bullseye and concentric circles, and some other formatting tricks to pull it off. Here's what it looks like:



All in all, not bad, I'd say.

ExcelGeek.com Insiders subscribers, your file's on it's way.

Dial / Gauge / Speedometer Chart

Many of you are no doubt aware that this is far from an original concept. Just google "Excel dial chart" or "Excel gauge chart" or "Excel speedometer chart" and you'll see the legions of others before me -- Jon Peltier, Andy Pope, Bill Jelen, to name some major influences on me -- who have done their own versions of this type of chart. Some even have released handy add-ins so that non-power users can easily create these charts for themselves. Nonetheless, I thought it'd be fun to put my own stamp on this type of chart and let my Insiders subscribers play with it. Here's a shot of the chart:



Users will be able to change it's size, scale, start and stop angles, show or hide major and minor gauge marks and add one or two "red zone" regions for the chart, among other features.

Have fun with it.

Later,

Excel_Geek