Posts Tagged ‘latitude and longitude’

Microsoft Excel “custom functions” can do many specialized tasks related to address and zip code information, such as checking the validity of mailing addresses, importing demographic information, even calculating driving time and driving distance.Custom functions, also referred to as UDFs (user-defined functions) perform complex calculations or tasks and are used in cell formulas just like the standard Excel functions SUM or AVERAGE. In this article we will describe how to use custom functions to identify which zip code (within a list of zip codes) is closest to a target location.

Identification of the closest zip code or address has many applications.For example, a mailing list of potential customers can be matched with the closest store locations, or a list of delivery addresses can be correlated with the closest distribution centers.These kinds of analyses require the geocode (latitude and longitude) coordinates of each zip code in order to calculate the straight-line “as the crow flies” distance between locations.After these distance calculations are completed for all possible combinations of zip codes, the one with the shortest distance is selected.

How can this be accomplished within an Excel worksheet?A custom function can automatically grab zip code and latitude/longitude data from a local database, and then perform distance calculations based on this data.Since all this occurs completely in the background, there’s no need to learn a new application or even leave the familiar environment of Excel. For example, let’s say we have a list of store zip codes in the worksheet range A1 through A10. We can find the store closest to a customer in zip code 08034 by inputting the custom function into a worksheet cell like this: “= CustomFunction (A1:A10, 08034)”.The result returned to the worksheet will be the closest store zip code from within the specified range A1:A10.

We can apply the same custom function to multiple customer zip codes listed in column B.If the first customer zip code is in worksheet cell B1, the formula in cell C1 would be = CustomFunction (A1:A10, B1).We can then simply copy and paste this formula all along the rest of the list to find the closest store for each customer.

This is a good example of how Excel custom functions can automate various tasks and calculations, with no need to learn a new application or even leave the familiar environment of Excel. From checking the validity of address information to finding the closest zip code to a target location, custom functions can be valuable tools for analyzing zip code and address information.

Author: Betty Hughes
Article Source: EzineArticles.com
Provided by: PCB Prototype & Manufacturing

The ZIP (Zone Improvement Plan) code is the postal code system first announced by the United States Postal Service (USPS) in 1963. The basic code format consists of five numerical digits which extended the postal zones that were adopted for large cities in 1943. In 1967, the ZIP code was made mandatory for second- and third-class bulk mailers. An extended ZIP + 4 code includes the five digits of the ZIP code, a hyphen, and four more digits for additional location precision. The public has not widely accepted this extended format which is not required except for certain presorted mailings.

Most mail is read by a multiline optical character reader (MLOCR) that almost instantly determines the correct ZIP + 4 code from the address and sprays an 11-digit Postnet barcode on the face of the mail item. This technology has greatly increased the speed and accuracy of mail delivery and kept costs nearly constant for over a decade.

People who send bulk mail can get a postage discount if they pre-print the barcode. Doing so is fairly complicated; mailing lists must be standardized with up-to-date certified software that adds and verifies a full, correct ZIP + 4 code and an additional two digits for the exact delivery point. The mail must be presorted in a specific scheme and be accompanied by appropriate documentation. This process usually requires certified software that also prints bar-coded address labels and bar-coded mail bag tags. WordPerfect word-processing software allowed users to print the barcode on outgoing mail. So did Microsoft Word prior to the 2007 version.

ZIP codes are used for mail tracking of mail and to gather geographical statistics. The U.S. Census Bureau has data that includes the latitude and longitude of the center-point of some ZIP codes (called ZIP Code Tabulation Areas or ZCTAs). While the Census Bureau does not keep up-to-date datasets of all ZIP codes this data is commercially available. If you are marketing an upscale product you may want to direct your campaign to a ritzy area such as Beverly Hills 90210.

Direct-mail campaign data are often used in a process called ZIP-code marketing, Point-of-sale cashiers sometimes ask consumers their home ZIP code. Such purchasing-pattern data has many uses such as determining where to locate new business establishments. ZIP-coded data are also used in analyzing geographic factors in risk, an insurance-industry and banking practice pejoratively known as redlining. Some software processes a user-input ZIP code and returns a list of business locations, usually in order of increasing distance from the input ZIP code.

The Post Office is now readying an Intelligent Mail barcode that will encode up to 31-digits of mailpiece data into 65 vertical bars. This new system promises several benefits including low-cost proof of mailing, payment tracking, more efficient customer care, more effective marketing, free address change service, mail preparation efficiency, and comprehensive mail piece tracking. In addition enterprises will obtain an extensive database describing their mail transactions. We may assume that sophisticated software will be developed to take advantage of this new technology.

Author: Levi Reiss
Article Source: EzineArticles.com
Provided by: Import duty tariff