Our Excel add-in CDXZipStream can return zip code radius and list data in the form of an Microsoft Excel array. An array is a group of related values that each occupy its own worksheet cell in an Excel worksheet.  It's often desirable to use arrays in cases where the returned data is too large for single worksheet cell.  We're going to review here how you can apply CDXZipStream arrays to large data sets.  This information applies to the right-click functions CDXRadius and CDXZipList, both of which allow the user to specify arrays as the output.

Let's say we're interested in finding all the zip codes within a 100 mile radius of target zip code 08033.  We can use the right-click function CDXRadius, with the following input:

The returned data is in an array two columns wide, showing both the zip codes within the radius in the first column, along with its distance in miles from 08033 in the second column.  Here's what the first few rows of the array look like:

If you click on any individual cell within the array, the formula is: {=CDXRadius("08033",50)}.  The custom function formula is surrounded by "curly" brackets indicating it is part of an array.  Note that the array acts as a single entity, such that you can't change or delete only part of the array; the array must be deleted in its entirety.

But what if we need to find zip codes within a 50 miles radius for a list of target zip codes?  It's possible to copy and paste the array to easily apply it to more than one target zip – here's how:

1.  Input the first zip code in cell A1 of a worksheet.

 2.  Right-click on cell A2, and select "Insert CDXRadius Function".

 3.  Use these inputs:

     Zip Code: A1

     Radius: 1000 (this will be changed later)

     Distance Unit:  distance in miles

     Result Out:  Array Formula

     Maximum Rows: 10000

4.  When you click OK, an array will be returned for the first zip code.   We purposely made this an extra large array with a 1000 mile radius, so when we copy and paste it for other zip codes we will be sure to have an array large enough to capture all the returned zips.  

5.  Now with the first array highlighted, use Excel's find and replace function to replace all "1000" values with "50" (while leaving out the quotation marks). 

6.  This array can now be copied and pasted immediately below any zip code to find the list of zip codes within 50 miles. 

You may want to experiment a bit with the size of the first array, by changing the radius distance or the number of maximum rows.  Just make sure it is large enough to capture all the data you need for subsequent target zips.

You can copy and paste the array manually, or record an Excel macro that can automate this process for you.  To prevent Excel from slowing down due to the large number of array formulas, just remember to occasionally copy and "paste special" the arrays as values as you proceed.

Even beyond their use in CDXZipStream, arrays can be powerful calculational tools for your worksheeets. For more general information about using Excel arrays, please check out the Microsoft website.

Radius analysis based on zip codes can be an extremely easy way to perform a variety of tasks, such as identifying customers closest to a store location, assigning client calls to sales representatives, or designing a direct-mail campaign for a charitable event.

Our Excel add-in CDXZipStream uses the custom function CDXDistance to calculate the straight-line distance between zip codes.  (Since this is a very fast calculation, thousand of zip codes can be processed very quickly.)  To help our clients better use this functionality, we are now offering a free Microsoft Excel template that employs CDXDistance to automatically filter zip codes based on their distance from a target zip.  

Just enter a list of zip codes in the template, specify the "target" zip code of interest and the radius distance (in either miles or kilometers), and the template automatically filters the list and shows only those zip codes that fall within the radius distance.  Any rows that contain zip codes not within the radius are hidden by Excel's autofilter function. 

There are also several blank columns available for other data associated with the address information in the template, such as name or telephone number.  These fields are filtered along with the provided zip codes and other address data.  After filtering, the results can be copied and pasted to a new Excel workbook; any hidden rows will not be copied to the new workbook.

As an example of how to use the template, we also offer a customized version which can identify nuclear facilities within a radius distance of a specified zip code.  In this case, we have provided (in the data entry area) zip code information for each nuclear facility located in the U.S.  As in the generic template, just enter a target zip code and radius distance, and only the nuclear facilities that fall within the specified radius will be displayed.

Both templates can be evaluated with the free demo version of CDXZipStream, and for longer-term use will work with the CDXZipStream Lite version.

MapPoint 2011 for North America was just released as of March 1, and we'd like to review here the features available for both 2010 and 2011 versions.  MapPoint is the Microsoft mapping software that works with our Excel add-in CDXZipStream, performing a variety functions within Excel such as driving distance and time calculations, route optimization, geocoding, map creation, and address verification.

The main difference between the 2011 and 2010 versions is the additional street update in 2011 – about 88,000 miles of roadways were added.  That represents about 2 percent of the approximately 4.8 million miles of roads in the US and Canada.  Particularly if you're using MapPoint for address verification or geocoding, the street update can be a significant factor in improving direct mail and customer outreach efforts.

If you're using an older, pre-2010 version of MapPoint, you may also want to consider upgrading to MapPoint 2010, which also includes updated street data over previous versions as well as some significant functional improvements.  For instance, MapPoint 2010 allows sharing of data with other mapping software or even portable GPS devices, by both importing and exporting gpx files.  It can also send location information to a mobile phone or GPS device.  MapPoint 2010 users can specify an easier-to-remember name for a route stop in lieu of an address, and can import more than 10,000 addresses into the software at one time, a limitation in other versions.  And best of all, pricing for the 2010 version is more than a few pennies cheaper than the 2011 version. 

MapPoint 2011 is currently available as a free trial from Microsoft, and you can directly compare it to the version you're now using – just specify that you want to keep your current version intact during the installation process.  And if you're ready to purchase either MapPoint 2010 or 2011, consider the excellent pricing available below from our partner site mp2kmag.com:

MapPoint 2010 Pricing

MapPoint 2011 Pricing

Custom functions, otherwise known as user-defined functions or UDF's, are an extremely useful but very often untapped resource in Microsoft Excel.  Just like standard functions SUM or AVERAGE, they are used in cells formulas to perform a variety tasks, some quite complex. The difference is that UDF's can be custom designed by and for you.  Sounds hard?  There is a bit of a learning curve, but if you are performing repetitive calculations throughout a worksheet, it can save you lots of time and frustration, and once you get your feet wet you will come to appreciate how easy it is to apply UDF's in  many situations.

For example, our Excel add-in CDXZipStream uses a custom function (designed by us!) to import data from an Access database into a worksheet cell.  The custom function to import the median age for the zip code 08034 looks like this:

  =CDXZipCode("08034","MedianAge")

Don't be too concerned about the fact that this is in a worksheet formula.  There is no math involved whatsoever.  The formula is simply calling a function that has a specified task to perform, and the parameters within the parentheses are communicating the details of how that task gets done.  In this case the custom function CDXZipCode is being called, and we're telling it to get age data for a particular zip code.  If we need data like this for a lot of zip codes, it is easy to see how using a custom function can be a great alternative to manually looking up data the old-fashioned way.

So what about that learning curve?  Custom functions are created using the programming language Visual Basic, which is relatively easy to learn and use. Visual Basic for Applications, otherwise known as VBA, is specific to MS Office applications like Excel.  The cool thing about VBA is that you can "record" a series of actions within your worksheet, and then use the resulting code as the basis of your custom function -  you don't have to create code from scratch, although you may have to modify the recorded code somewhat so it can be used in all situations.  There are also numerous examples of VBA code within the Help function of the Visual Basic editor in Excel, and even more on the Web and other resources.  We guarantee, once you create your own UDF with Visual Basic and see how simple (and powerful) it is, you'll soon envision a whole range of functions that can be used within your worksheets.  To get started, here are some great resources:

A Microsoft tutorial for creating UDF's

Step-by-step instructions for macros and UDF's from Marshall Business School, USC

A basic, simple example from ExcelTip.com

Some nice examples of UDF's (and their VBA code) from FontStuff.com

VBA programming reference books from John Walkenbach

And last but not least, one of our YouTube videos showing CDXZipStream custom functions in action:

Zip Code Finder in Excel

In a past post we briefly discussed (in an admittedly negative way) the ribbon GUI that first appeared in Excel 2007 and is now entrenched in all Office 2010 applications.  Well, around here we started to talk a little more about it and it's pretty clear that the ribbon is one of those things (like anchovies on pizza or the New York Yankees) that you either love or hate – there's not a lot of middle ground. 

The real issue is that the ribbon is a major hurdle to many Excel users who would like to upgrade to a later version of Excel to take advantage of new features.  So let's talk about the ribbon in a little more detail and see if we can help with the decision-making process in upgrading to either Excel 2007 or Excel 2010.

So, what about that gosh-darned ribbon?  First of all, if you really don't want to use the ribbon, you don't have to.  Third-party vendors have stepped in with relatively inexpensive (about $15 to $30) add-ins that can recreate the classic toolbar in either Excel 2007 or 2010.  Some versions can be used in all Office 2010 applications and can even be deployed among multiple computers; in at least one case (ToolbarToggle) the toolbar is customizable just like in older versions of Excel.     

Keep in mind, however, that many people do like the ribbon and you should probably consider trying it before buying a toolbar add-in.  If you decide to use the ribbon, be prepared to spend some time learning it.  We don't recommend doing this by the seat-of-your pants – sit down at your computer with your favorite caffeinated drink (you'll need it) and go through some good training materials.  Some possibilities are the Chandoo.org Master the Excel 2007 Ribbon free learning guide, and Microsoft's Up to Speed with Excel 2007 learning tool.

But beware:  Microsoft decided (what were they thinking?) that the ribbon in Excel 2007 could only be customized using xml (Extensible Markup Language) and programming code.  Many Excel users save precious time and significantly improve their productivity by customizing that classic toolbar to fit their particular needs; did Microsoft expect the typical user would want to learn xml too after going through the ribbon learning curve?  You can also purchase add-ins that do this programming for you, but fortunately this is not an issue with Office 2010 and the ribbon can now be customized as easily as the classic toolbar.

There are lots of other things to consider if and when you decide to upgrade. You can find more than a few discussions on the web weighing the pros and cons of the various versions of Excel, and that is beyond our scope here.  Ultimately, many users who are perfectly happy with their trusty old software (remember Windows 98?) will upgrade simply because of the inconvenience of using an outmoded version, especially with the loss of technical support and compatibility. At the end of the day, there are thankfully a few inexpensive workarounds to address most concerns about the ribbon – it's just too bad that something as basic as the user interface had to be an issue at all.