Heat Charts are so easy in Excel 2007

Before you roll your eyes and say, "Welcome to life after 2003, Excel_Geek," let me just say, "I know, I know." The driver for this project was that I was analyzing my website traffic using my Google Analytics account. I noticed a few things.

One was that "heat chart" or "heat charts" seems to be a pretty popular search term for folks to find me. I've obviously done a couple of heat chart projects, but back when I was still using 2003, so in order to create them with more than four colors (the limit to Conditional Formatting in 2003), I had to do write some VBA code to accomplish it.

Another thing I noticed what the rhythmic up and down of my site traffic. There were a few major spikes -- March Madness season, college football bowl game season, and one recently when @MSExcel mentioned me in their Twitter feed. What I wanted to further analyze, though, was if there was any pattern to traffic patterns based on Day of Week and recency of blog posts.

I combined these two findings and decided to do a heat chart incorporating both Day of Week and recency to blog posts. Here's what the chart itself looks like:



Along the way I used some other favorite techniques along the way: indexing the data (so I'm not sharing my actual web stats...hey, that's my business), a trick to convert a list of dates into a list of days of the week, VLOOKUP using TRUE instead of FALSE (whatever for? right?), and using SUMPRODUCT to create COUNTIFS and SUMIFS functions that will be backward compatible to Excel 2003 and earlier. I highlighted the creation of the heat chart and these other techniques in the source file, which I'll be sharing with my Insiders Subscribers.

Later,

Excel_Geek

2011 March Madness Bracket is coming soon

Mark your calendars, folks. March 13th is Selection Sunday. In what has now become a five year tradition, I'll be releasing the ExcelGeek.com March Madness Bracket spreadsheet late that evening, once the picks are known.

In years past I've charged $3 for the "Master" version of the sheet -- the one for the organizer of your office pools, etc. I used the opportunity to showcase (read: show off) a system I've developed for locking down and protecting, then individually licensing access to my spreadsheets. I plan to keep it locked down (damn, right?), but I'm dropping the price for the Master Spreadsheet to $2. Once you pay via PayPal and send me the "Lock Code" given to you by the spreadsheet once you open it (and have your Macros enabled properly), I'll send you the "Key Code" to gain access.

Can't wait...

Excel_Geek

Updated: Parse Google Search Results for First URL

Those of you who've been following me for some time may know that I've done quite a few projects related to parsing Google search results for various data -- URLs, number of results, etc. Those of you using the code likely know that some time ago Google apparently made some changes to how they put together their site causing my stuff to stop working. Well, recently one of the requesters of one of these projects - one that returns the first non-paid URL returned - came back and had me update it to work with how Google's site currently displays search results.



This time I made it into a user-defined function. You simply enter the function...



=getFirstGoogleResultURL(A2), where the value of cell A2 is the search term.


It works pretty slick, but know that Google will not allow this sort of thing to be used on large volumes of searches. I'm pretty sure they'd much rather have you using their APIs for this sort of usage.

Excel_Geek Insiders subscribers, your copy of this file is on its way.

Later,

Excel_Geek

Introducing Gadi, My First ExcelGeek.com Contributor

Everyone,



I'd like to introduce you to Gadi Bizinyan.




Gadi is originally from Isreal, but is currently working in Bangkok, Thailand. Gadi's got a Bachelor's degree in Economics and Business Administration and really got into Excel as part of that curriculum. His self-described specialties in Excel are creating dynamic databases, manipulating data, analyzing statistically and graphically, and financial analysis.



Something you may be surprised to learn is that Gadi's been helping me for some time now. Some $50 Projects and some Private Projects have been his handywork. Remember, I've got a full-time "real job" as Director of Sales for Stanley Healthcare Solutions, so I cannot dedicate nearly as much time to this overgrown hobby as I'd like. Gadi's helping me to continue to help people in need and do some neat projects. Gadi's also going to begin contributing posts to the blog, too. Please welcome him and pretend you like his stuff as much as mine. ;-)



Welcome aboard, Gadi!

2010-11 College Football Bowl Pool Spreadsheet

Looking for the 2011-12 files? Go here.

Okay, folks. You've waited long enough. Here's this year's college football bowl pool sheet:



2010-11 Bowl Pool Summary Sheet



And here's the individual picks sheet you can pass around to those participating in your pool to copy-paste their picks back into the Summary sheet file (just remember, Copy --> Paste Special... --> Values is your friend):



2010-11 Bowl Pool Individual Picks Entry Sheet



As always, if you find any issues, please let me know. If you've got a cool idea for an enhancement, do the same.



Enjoy,



Excel_Geek

Yes, I'm doing a college bowl pool spreadsheet

Recent MeeboMe inquiry:

[12:25] meeboguest######:Are you going to have a college bowl spreadsheet for this year since there is one extra bowl from last year? The spreadsheet is wonderful.


Yes. Don't worry folks. There will be a new 2010 NCAA Football Bowl pool spreadsheet. Any ideas how to make it cooler than last year's?

SUMPRODUCT that only includes visible rows

The other day a reader contacted me through my MeeboMe chat window. He (or she...I'll use "he" throughout for simplicity) wanted help transforming a SUMPRODUCT formula that he currently uses to be useful in situations where the data is filtered or for some other reason certain rows are hidden. Basically he wants the formula only to work on rows that are visible. Now, this reader reached out a couple of times, but I was never able to get him a solution (I had some clarifying questions.) before he went silent on me. I thought providing a solution to all of my readers on this issue might be warranted, though. This is a fairly common issue Excel users might face in real life.

The answer, basically, is the SUBTOTAL function coupled with the OFFSET, ROW, and MIN functions being embedded into the SUMPRODUCT function. Whoa! Right? Let's break this down.

SUMPRODUCT works like this:

=SUMPRODUCT(<Range1>,<Range2>,[<Range3>...])

The values in each row of each range are multiplied together and then added up. In its simplest form, it's a great way to calculate things like weighted averages. The problem is that is will include every row in the ranges, even those hidden due to filtering or otherwise.

What do we know that doesn't include hidden rows? That's right SUBTOTAL has options to exclude hidden rows. Using subtotal type "109" is a sum that ignores hidden rows. So here's how to incorporate SUBTOTAL with OFFSET and MIN to make this work:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(<Range1>,ROW(<Range1>)-MIN(ROW(<Range1>)),,1,1)),<Range2>)

The crazy OFFSET, ROW, MIN combo is a way to coerce Excel to treat each cell in Range1 individually, like it does automatically when simply referencing the range in SUMPRODUCT, while still having the doesn't-count-hidden-rows benefit of SUBTOTAL type 109.

It also occurred to me that many Excel users may be using SUMPRODUCT to perform summing of data based upon multiple criteria -- you know, SUMIFS before Excel 2007 came out and we had SUMIFS. Here's how you'd do that using this technique:

Doesn't exclude hidden rows:

=SUMPRODUCT((<Range1>)*(<Range2>="West")*(<Range3>="Product A"))

This would add up "Sales" (Range1) where the "Region" (Range2) is equal to "West" and the "Product" (Range3) is equal to "Product A".

Excluded hidden/filtered rows:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(<Range1>,ROW(<Range1>)-MIN(ROW(<Range1>)),,1,1))*(<Range2>="West")*(<Range3>="Product A"))

I hope this explanation has been helpful. Excel_Geek Insiders subscribers, I'll fire you off a sample spreadsheet illustrating these techniques.

Later,

Excel_Geek