Posts Tagged ‘excel microsoft’

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

Microsoft Excel “custom functions” can do many specialized tasks related to address and zip code information, such as checking the validity of mailing addresses, calculating driving time and driving distance, even importing demographic information.Custom functions (also called User-Defined Functions or UDFs) perform complex calculations or tasks and are used in cell formulas just like the standard Excel functions SUM, AVERAGE, or LOOKUP. In this article we will describe how you can use custom functions to create lists of zip codes in Microsoft Excel.

Zip code lists can be used for a variety of purposes, such as planning direct mail or advertising campaigns.But instead of cutting and pasting zip codes from a website or database, custom functions in Excel can automate interactions with a zip code database and import zip code lists easily into a worksheet, allowing the user to then analyze the zip codes and any associated data using Excel’s powerful built-in capabilities.From the user’s point of view, these custom functions look just like another formula in the worksheet.Since all interactions with the database occur in the background, the user can work entirely within the familiar Excel environment – there’s no need to learn a new application.

How would this work within an Excel worksheet?Let’s say we need a list of all the zip codes in the city of New Rochelle, in Westchester County, New York.To use a custom function called ZIPLIST, we just type this formula into a cell “= ZIPLIST (“New York”, “Westchester”, “New Rochelle”)”.The function we call ZIPLIST gets the required data from a local database and returns all the New Rochelle zip codes to the worksheet cell. If we need zip codes for a long list of cities in a worksheet, we can also input a custom function formula using the only worksheet cell locations, such as “=ZIPLIST (A1, B1, C1)”, where A1, B1, and C1 refer to the cell locations of the state, county, and city, respectively.We can then easily copy and paste this formula to apply it to all the cities in our list.

Zip code lists for a county or state can also be generated using this method.For all zip codes in the state of California, the custom function formula is”‘=ZIPLIST (“California”).The omission of county and city information in this case indicates only state-level information is required.

This is a good example of how Excel custom functions can automate specialized 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 generating zip code lists in Microsoft Excel, custom functions can be valuable tools for analyzing zip code and address information.

Author: Betty Hughes
Article Source: EzineArticles.com
Provided by: Electric Pressure Cooker

Microsoft Excel “custom functions” can be used to carry out many tasks related to address and zip code information, such as importing demographic data, checking address accuracy, even calculating driving time and distance. Custom functions, also referred to as User Defined Functions (UDFs), perform complex calculations or tasks and are used in cell formulas just like standard Excel functions such as SUM or AVERAGE. In this article we will discuss how custom functions can also be used for identifying zip codes within a specified distance or “radius”.

Zip codes can be extremely useful for analyzing demographic information, and can help you better target customers and plan marketing campaigns. For example, identifying zip codes within a specified radius of potential new store locations can help you select a location most accessible to your customer base. But instead of drawing circles on a map, a much easier approach is to use a custom function within Excel that will identify the zip codes for you.

How would a custom function like this work? Let’s say you are evaluating a possible store location in zip code 07470 and need a list of all zip codes within a 10 mile radius. To use a custom function named RADIUS, simply input a formula in a worksheet cell like this: “= RADIUS (Zip Code, Radius Distance)”. For this case the formula is “= RADIUS (“07470″, 10)”, and the worksheet will display all zip codes within 10 miles of 07470.

The formula can be tailored to output the data as a string of zip codes listed in a single cell, or as an array with individual results in multiple cells in the spreadsheet. To return a formula as an array in Excel, simply highlight the destination range with your mouse, then hold down the Ctrl and Shift keys and press Enter. The returned data can also include the distance as well as the zip code and sort these by closeness to the target zip code. Additional options can be added to have the distance returned in kilometers or miles.

The custom function automates the process of finding all zip codes that fall within the given radius by searching a local zip code database and performing distance calculations based on latitude and longitude. All of these actions occur in the background, so the user works entirely within Excel and sees only the resulting list of zip codes displayed in their worksheet – there is no need to learn a new application or import a zip code list from a web page.

A big advantage of custom functions is their ability to process large amounts of data in a spreadsheet format. To perform zip code radius calculations for a list of zip codes in column A, for example, simply input the appropriate custom function formula in cell B1, such as: “= RADIUS (A1, 10)” where “A1″ is the worksheet cell location of the first zip code. Then just copy and paste this formula as needed in column B; in this way you can perform radius calculations for literally thousands of zip codes, without the time-consuming manual input required for other programs.

There are other useful Excel custom functions that complement zip code radius calculations by calculating the distance between two given zip codes. The custom function formula in this case would be something like “= DISTANCE (Zip Code 1, Zip Code 2)”. The calculated distance can be “as the crow flies” or can be estimates of actual driving distance or time.

A zip code radius function is one more good example of how Excel custom functions can be used to automate complicated tasks with no need to learn a new application or even leave the familiar environment of Excel. From checking the accuracy of mailing lists to identifying zip codes within a specified radius, custom functions can be valuable tools for analyzing zip code and address information in Excel.

Author: Betty Hughes
Article Source: EzineArticles.com
Provided by: Pressure cooker