Pick the best meeting location in Excel

In order for the technique on this page to work, you must purchase and install Spheresoft Zip Code Distance and Radius Tools

Suppose you have branches all over the country and want the pick a meeting location that minimizes the average travel distance. You might have a spread sheet that looks like this:

The list of locations is down the side, with the # of attendees from each location in column D. Across the top, we have the potential places (Detroit, New York, Austin and Sante Fe), and their zip codes. Follow these steps to find the best meeting location:

1. In E3, put the formula =ZipCodeDistance(E$2,$C3)*$D3
   you need to use dollar signs, so that when you copy, the source zip codes anchor to the headers.
2. Copy E3 to the E3:H11.

ZipCodeDistance is a formula you get with Zip Code Tools. We are multiplying it by the # of attendees so that we get the total distance travelled by everyone in that location. It should look something like this:

Now to find the best place calculate the average distance for each location and the standard deviation.

1. In cell E12 put the formula =AVERAGE(E3:E11)
2. Copy E12 to F12:H12
3. In cell E13 put the formula =STDEV(E3:E11)
4. Copy E13 to F13:H13

It will look like this:

In this case, Detroit has both the lowest average and the lowest standard deviation. If two places had a close average, the standard deviation would tell you if some people are travelling much further than others.

If you have Spheresoft Zip Code Tools installed, download this sheet to see a live example.

(Back to the ZipCodeTools home page)

$65 - immediate electronic delivery

Not what you were looking for? Please send us your feedback!