Monthly Archives: September 2013

Excel Power Map Sample

Posted on by
7

Yesterday, Microsoft released a preview of Power Map; a geo-spatial mapping Excel add-in, formerly know as GeoFlow.  It’s a pretty cool add-in that allows you to plot data based on geographic identifiers (longitude, latitude, country name, town names, postal codes, etc), and show it on a 3D or 2D map.  It works with tabular data sets, whether they be from and Excel table, database or Power Pivot.

The other cool thing is that, once you get it right, you can actually produce a video that can be shared with non-Excel users!  I’ve uploaded one of those to YouTube so you can check it out.

This video shows the wind speeds of hurricane Sandy as it travelled through the Caribbean to it’s eventual landfall on the USA’s east coast.  I used a heat map to show the speed (the redder it gets the faster), and played it over time so you can trace the path.

Pretty cool stuff!

If you have Excel 2013 Pro Plus, you can download the preview here:  http://www.microsoft.com/en-us/download/details.aspx?id=38395

OFFSET or Named Range – Which would you use?

Posted on by
5

I’m working on a spreadsheet where users will be able (required) to insert new rows at a later date.  When they do so, it’s critical that the section subtotals always… well… subtotal correctly.

The challenge, of course, is that you can’t rely on newly inserted rows being picked up by the subtotal formulas, so someone needs to check them.  At least, you can, but it takes more than just a SUM or SUBTOTAL formula to get it done.

I reached back to the method using a named range that I describe in the “Always Refer to the Cell Above” Excelguru KB Entry, resulting in a formula that looks like this:

SNAGHTML958c98

Of course, I don’t actually need to use the named range to do this.  I could make it work by using the OFFSET function in L66 as follows:

=SUBTOTAL(9,L62:OFFSET(L66,-1,0))

Either will work just fine, and will not be tripped up by a user inserting a new row within my boundary, so I should never (okay, never say never) run into an issue with this particular problem.

I’m curious which method you would use?  Named Range or OFFSET, and why…