Excel Tips & Tricks

Using Custom Functions to Validate Addresses

2011-06-24T00:19:00.000-07:00

Microsoft Excel "custom functions" can do a variety of things related to address and zip code information, such as importing demographic data, finding zip codes within a radius, even calculating driving time and distance. Custom functions (also called User-Defined Functions or UDFs) perform complicated calculations or tasks and are used in cell formulas just like the standard Excel functions SUM, AVERAGE, or LOOKUP. In this article we will show you how to use custom functions, in conjunction with Microsoft MapPoint, to check the validity of street addresses.

Incorrect address information is a common problem. The street or city names can be misspelled, the zip code may not match the city information, or the street number may be invalid. You can use a web-based program like MapQuest or Google Maps to check the validity of a single address, but for multiple addresses in a mailing list or delivery route, the best approach is to use an automated program that can check large lists of data all at one time.

An Excel custom function working in conjunction with a stand-alone mapping program like Microsoft MapPoint can validate long lists of addresses automatically and return the results directly to your worksheet. Since all interactions with MapPoint occur in the background, you can work completely within the Excel environment - there's no need to learn a new application. For example, to check the validity of an address in worksheet cell A1, you can input the appropriate custom function formula (inserted in cell B1, for instance) which would look something like this: "= CustomFunction (A1)". If the address appears to be valid, the "best match" address is returned to cell B1. If no match is found, the message "Invalid address" is returned. MapPoint also has the capability to provide best match information even when there is a minor error in the address. In cases where there is a city or street misspelling, or transposed numbers in the zip code, the best match returned to the worksheet will have these errors corrected.

For a long list of addresses in column A, the custom function formula can be copied and pasted as needed in column B, so you can automatically validate many addresses without the time-consuming manual input required for other mapping programs.

It is also possible to specify that other types of data be returned to the worksheet if the address is determined to be valid. For example, geocode information for the address, such as latitude and longitude, can be returned. Street name, city, state, zip code, or country information can also be returned, providing a reliable way to parse the address into separate worksheet cells.

This is a good example of how Excel custom functions can use other applications like Microsoft MapPoint to 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 identifying zip codes within a radius, custom functions can be valuable tools for analyzing zip code and other location-based information.

Please see the YouTube video at Address Validation in Excel for more information about using Excel custom functions for address validation. The author of this article, Betty Hughes, helped develop CDXZipStream, an Excel add-in that provides zip code data, demographics by zip code, driving route optimization, zip code radius analysis, geocoding and more using custom functions. A free 30-day trial is available as a download from our website, as well as example spreadsheets and tutorials showing how to use custom functions in a variety of applications.

Copy and Paste Tips

2011-06-20T00:18:00.000-07:00

When you are using Microsoft Excel 2007, you usually need to copy a part of the content to other location in the worksheet or copy to another worksheet, sometimes different workbook. You can do it easily by using the copy and paste function in Microsoft Excel 2007.

Let's open a blank worksheet (by now you should be able to do it by just opening Microsoft Excel 2007 or by clicking on Office Button and select New). We have to input some content into the worksheet before I could demonstrate the Copy and Paste functions to you. Let's just randomly key in "Copy" into cells from A1 to A10. Now you have a series of text "Copy". Say now you want to copy the whole series of text "Copy" to Cells D1 to D10.

Follow the simple steps as what I am going to show you now. Firstly select cell A1 to A10, then on your Keyboard, press Ctrl+C to copy the cells to the Clipboard. Alternatively you could put your cursor on the select area (A1 to A10), Right Click and select Copy, Or you could select Home-- >Clipboard-- >Copy.

You will find that the selected areas are now covered by dotted line (this is the indication that the area selected for copying). Next is move the cursor to cells D1 and press Enter (Alternatively you may want to Right Click on cell D1 and select Paste, or you could follow the path Home-- >Clipboard-- >Copy ), you will paste the content of A1 to A10 all the way to D1 to D10.

Copy and Paste functions are not only limited to text only, you can copy chart, formula, logical functions and any other features in Microsoft Excel 2007 to any location you prefer within Excel.

Luis T is the owner of http://www.excelexpertuser.com. Find out more on how you could be a master of Microsoft Excel on the website.

The Many Uses of Spreadsheet Software

2011-06-16T00:16:00.000-07:00

Whether you work at an accounting firm, a marketing company, an auto dealership, a school attendance office, a manufacturing plant's human resources department, or an office associated with city, county, state or federal government, chances are, you'll be called upon to use and learn Excel.

Just about every workplace has a demand for Excel, the computing world's most commonly used software program for comparative data analysis. Excel has been available in various incarnations for more than a decade. Each subsequent release takes the program to new territory.

Popularly known as the best spreadsheet program on the market, Excel is powerful, easy to use, and remarkably efficient. Excel is highly interactive. Its spreadsheet cells are arranged in a collection of rows and columns, each of which can hold a number, a text string, or a formula that performs a function, such as calculation. It's easy to copy and move cells as well as modify formulas. The spreadsheet is displayed on the computer screen in a scrollable window that allows the document to be as deep or as wide as required.

Working for a major newspaper in Northern California, I was one of several reporters involved in the annual evaluation of our county's economy. The job involved collecting data that would be punched into Excel spreadsheets that ultimately ranked information according to the category of statistics being reviewed.

The beauty of Excel, from the standpoint of newspaper research projects, is that you can use formulas to recalculate results by changing any of the cells they use. With this model, you can use the same spreadsheet data to achieve various results by simply defining and changing formulas as desired. It is this feature that makes Excel so useful in so many different arenas.

With a click of the mouse, we reporters were able to get answers to a wide variety of questions. Which employers had the greatest number of workers? Which ones had the highest amount of gross annual receipts? Which ones appeared to be growing and which ones had declining sales? What was the volume of real estate loans and had there been a decline or increase from the previous year?

We looked at local and national retail, services, financial institutions, government entities, agriculture, the wine industry, tourism and hospitality, manufacturing, residential and commercial real estate, everything imaginable.

Excel allowed us to examine ratios, percentages, and anything else we wanted to scrutinize. Finally, we were able to use Excel to compare the results to data from previous years.

Since reporters tend to be former English majors, most of those who worked on this annual project were more familiar with Microsoft Word than any other software program. Therefore, most were required to undergo Excel training. For some, learning Excel was easier than for others. A few relied on guides such as Microsoft Excel Bible. Some reporters underwent an Excel tutorial while others learned by doing.

Not only were the Excel spreadsheets crucial to the research, the format of each was published in the newspaper. Here is where some additional Excel functions came into play. Editors were able to make the spreadsheets more visually appealing by using colors and shading, borders and lines, and other features that made the spreadsheets easy for readers to decipher.

Wearing another of my several hats in the newsroom, I often wrote articles concerning the local job market. I found proficiency in Excel was a requirement for a wide variety of employment positions and that area recruiting firms offered their clients opportunities to take free or low-cost Excel tutorials in preparation for the workplace. Most employers expect job candidates to already know the software that the work will require and don't want to have to train new hires.

Don't kid yourself. If you're seeking any kind of office work, you'll need to know not only Microsoft Word but also Excel.

Excel and Microsoft are trademarks of Microsoft Corporation, registered in the U.S. and other countries.

About Video Professor: Video Professor is the industry leader in self-paced computer software tutorials. Since 1987, the core of Video Professor's learning initiative has been to provide home users as well as corporate professionals with valuable and user-friendly learning solutions for today's most common software titles. Tutorials are offered on CD-ROM and through Video Professor Online, an Internet-based program.

About the Author: Sheri Graves is an award-winning writer who freelances from her home in Santa Rosa, California.

Using the CONFIDENCE Formula for Intervals

2011-06-12T00:14:00.000-07:00

Confidence intervals often give you useful insights into data sets you're trying to better understand. A confidence interval is the interval around a sample mean into which you expect the population mean to fall a certain percentage of the time.

If you have a sample of size n and know the sample mean m and population standard deviation sigma (s), you can find the range into which the actual population mean will fall x% of the time. Common confidence levels are 90%, 95%, and 99%.

Using the Confidence Function

The CONFIDENCE function uses the following syntax:

=CONFIDENCE (alpha,s,n)

Alpha is the significance level. It equals 1 minus the confidence level (expressed as a decimal). The s argument is the standard deviation of the data set. The n argument gives the number of items in the sample.

An Example Confidence Interval Calculation

For example, if a sample of 500 college graduates shows that they owe an average of $12,000

in student loans at graduation and the population standard deviation is $2,000, you can find

a 95% confidence interval estimate of the population mean amount owed.

To do this using the CONFIDENCE function, enter alpha .05 as the first argument, the standard deviation 2000 as the second argument, and n 500 as the third argument. The function looks like this:

=CONFIDENCE (0.05,2000,500)

The function returns the value 175.30. So you can say with 95% confidence that the population

mean is $12,000 plus or minus $175.30. (Note that if you have Microsoft Excel installed on your computer, you can copy the text shown above and paste the text into an Excel cell to make the calculation on your computer.)

One final note should be made here: If the value of the population standard deviation is unknown, you can use the value of the sample standard deviation as the point estimate of the population standard deviation.

About the author: Seattle accountant and author Stephen L. Nelson wrote the MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself business incorporation kits that entrepreneurs and investors can use for setting up a Incorporating in Kansas or a Incorporating in Kentucky.

MapPoint and Excel to Calculate Driving Distance

2011-06-08T01:59:00.000-07:00

Microsoft Excel "custom functions" can be used to carry out many tasks related to address and zip-code based information, such as importing demographic data, checking address accuracy, even identifying zip codes within a radius. Some of these are discussed in the EzineArticle "Using Microsoft Excel to Manage Mailing Lists" by my colleague, Ian Roberts. Custom functions, also referred to as User Defined Functions (UDFs), perform complicated calculations or tasks and are used in cell formulas just like the standard Excel functions SUM, AVERAGE, LOOKUP, etc. In this article we will review how custom functions can also be used for calculating driving distance and driving time between addresses listed in an Excel worksheet.

Let's say you have lists of addresses in Excel that require analysis based on driving distance, driving time, or both. Such an analysis could support a variety of purposes - to estimate shipping costs for your business, optimize delivery service routes, even help plan sales calls. You are probably familiar with internet-based mapping services such as MapQuest or Google Maps where you input start and end points to obtain driving directions, mileage, and estimated driving time. This works fine for a single pair of addresses, but for larger sets of data an automated approach is needed.

A custom function working in conjunction with a mapping program like Microsoft MapPoint can calculate driving distance or time for various route preferences (such as shortest distance or quickest driving time) and automatically return the result to your Excel worksheet. It isn't necessary to learn a new application, since all interactions with MapPoint happen in the background; you work only within the familiar Excel environment. For example, to calculate driving time between addresses listed in worksheet cells A1 and B1, simply input the appropriate custom function formula (inserted in cell C1, for instance) which would look something like this: "= CustomFunction (A1, B1)". If you have multiple pairs of addresses in columns A and B, just copy and paste this formula as needed in column C - in this way you can automatically obtain driving distance or time for literally thousands of sets of addresses, without the time-consuming manual input required for typical mapping programs.

This type of function can also calculate routes with specified stopping points along the way, to simulate a real-life delivery route, for example. In this case, just list the addresses according to their order on the route, in a custom function formula such as "= CustomFunction (Address 1, Address 2, Address 3, etc.)". To optimize the route, you can change the address order to see the effect on driving distance or time.

In situations where exact addresses are not available, custom functions can also return driving time or distance using more general addresses based on street name, city, or zip code. The route calculation uses the geographic center of the given address. Address types do not need to be consistent within a single custom function formula. Examples of valid addresses are: "20015" "Louisville, KY" "Washington Street 02121".

To sum up, this is an excellent example of how custom functions in Excel can tap into the power of other programs, such as Microsoft MapPoint, while allowing the user to work within the familiar Excel environment. From checking the accuracy of mailing lists to calculating driving distance and time, it's easy to see how custom functions can be valuable tools for analyzing address information in Excel.

Please see the YouTube video at Driving Distance Calculator in Excel for more information about using Excel custom functions for calculating driving distance and time. The author of this article, Betty Hughes, helped develop CDXZipStream, an Excel add-in that provides zip code data, demographics by zip code, driving route optimization, zip code radius analysis, geocoding and more using custom functions. A free 30-day trial is available as a download from our website, as well as example spreadsheets and tutorials showing how to use custom functions for calculating driving distance and time.

Training to Use Excel Professionally

2011-06-04T01:57:00.000-07:00

Microsoft Excel is a spreadsheet and data storage software program that is an important part of the business management process in many organizations. An ability to understand this program and to work with it is essential for all levels from entry to top management. Many organizations list being able to use Excel as a requirement for employment. The ability to master this useful program will benefit anyone involved in business from a job applicant to the CEO of the company.

Excel training exists at many levels also. The software itself includes tutorials designed to get you started. The Microsoft website offers additional training software that is also intended to give a user at least a working knowledge of Excel. For many people, this is as far as they take their training and as a result they both struggle with Excel and fail to realize or utilize its tremendous potential. If you want to get the most out of it, you need to accept its complexity and recognize the need for some serious training. If you are able to reach the point where you can truly say you have "mastered" Microsoft Excel, you will have a leg up on the competition both within and outside of your company.

Microsoft Office training is offered through a worldwide network of Microsoft Certified Learning Partners. These organizations have passed rigorous Certification examinations administered by Microsoft. There are more than 26,000 Learning Partners in this network. Training is administered in two different manners. There is classroom based training and distant learning. These two methods have become a common choice in most computer based training and educational programs in the last decade. Each individual must evaluate his own situation taking into consideration his budget, his schedule, and his own ideal learning style to make a decision about which is better for him.

Microsoft Excel training is offered by either group. The Microsoft Certified Partners for Learning Services (MCPLS) offer comprehensive hands on instructor led training at sites spread widely over the world. A directory on the Microsoft Excel training home site can guide you to a MCPLS facility in your area. Microsoft Official Distant Learning (MODL) partners provide training platforms that can be delivered directly to your own computer. They provide the back up and technical support needed to complete the training and maximize its results.

It is possible to delve into Microsoft Excel if you have any knowledge or familiarity with spreadsheet applications, but this approach ignores the potential and only skims the surface of what Excel can do for you and your organization. The smart way to master Excel and get the most out of it is to take some formal Excel training from a Certified Partner.

Natalie Aranda writes about computers. Microsoft Office training is offered through a worldwide network of Microsoft Certified Learning Partners. A directory on the Microsoft Excel training home site can guide you to a MCPLS facility in your area. Microsoft Official Distant Learning (MODL) partners provide training platforms that can be delivered directly to your own computer.

How to Create Relative and Absolute References

2011-05-31T01:14:00.000-07:00

Microsoft Excel is built on a regime of Columns and Rows with the intersection of these two elements giving us our cells. The cells in Microsoft Excel are always named Columns and then Rows, so a typical cell address would look something like - AB256. This particular cell is found on column AB and in row 256. The reference I wrote there is very important as it tells me something significant, that is, it tells me the cell address is a relative cell address. So what does this mean? Well there are two types of references used in Microsoft Excel which is an Absolute Reference and a Relative Reference. On a day-to-day basis, the relative and absolute reference doesn't really affect the operations of your spreadsheet. Where it does become a noticeable issue is when you start copying formulas from one cell to another.

Lets investigate the difference between the two ...

A Relative Reference is one that when copied from one position to another will adjust the formula cell address to suit the position it is in. For example if you have a formula in cell address C4 that was =B4*C1 and then copied the formula into say C5 what you will notice is that the cell addresses of the formula will change to =B5 * C2. The reason this has occurred is that the cell addresses are in fact relative addresses. That is the cell address is relative to its current position.

To change a cell address from being relative to being absolute we simply add dollars signs to the cell address in this way - $C$5. What this address is now saying to us is that we must absolutely refer to column C and absolutely refer to Row 5. This means that if we apply the dollar symbols to our previous formula in cell C4 which is =$B$4 * $C$4 and then copy our formula to cell address C5 you will notice this time round that the cell addresses didn't change. The reason is that by adding the $ symbols to the cell address we are telling the formula that regardless of where you place the formula you must absolutely refer to Column B and Row four thus we have $B$4.

There are many reasons why you may use Relative References over Absolute references and vice versa. One of the most common one for using absolute references is when you have a specific value you want to refer to in a formula. For example, lets say you are building a mortgage calculator and you need to refer to an Interest Rate. To ensure you are always referring to the right cell that has the interest rate value you may set an absolute value.

In Microsoft Excel there is also a reference called a mixed reference. Essentially what this means is that only either the Column or the Row has the dollar symbol, for example $C3. What this is telling us is that in the formula you must absolutely refer to column C but the value in the row is relative to the position of the formula.

There are a number of ways that you can enter Relative and Absolute values into a formula. One technique is that you can simply type the $ symbols next to the Row or the Column. However there is an alternative. Once you have typed in the Cell address like C4 you can move back into the cell address and then use the F4 key to toggle the cell reference from Relative to Absolute to a Mixed References.

Absolute and relative references are extremely important in Microsoft Excel and they ensure that the formulas you are creating actually refer to the correct cells. Remember one simple rule, if the cell addresses have a $ symbol next to it, it means you must absolutely refer to either the column or the row.

Chris Le Roy has available Microsoft Excel Shortcuts to help you with Microsoft Excel. To learn more about the IF Function or to get a Certificate in using Microsoft Excel in just 16 hours, simply check out Chris's correspondence course where you can earn yourself Microsoft Excel Certificates issued by his company without evening leaving home - Microsoft Excel Spreadsheet Training

Tips on Microsoft Excel are also Available.

Excel Databases - Using Spreadsheets!

2011-05-27T01:12:00.000-07:00

The term Microsoft Excel Database has never really sat very well with me since my primary role in the IT world has been in developing database and what is used by the spreadsheeting fraternity is what I consider an over glorified list. However, for the sake of peace, I want to outline some of the principles you must follow in building what is known as the Microsoft Excel Database.

Let us review ...

A database in essence is what is considered a collection of information that is related in some manner. For example if you were running a company and selling a product you may have a database that simply lists all of the sales you have made over a period of time. Storing this data in a database would make sense as the company information and the selling of a product is related and as such would be appropriate for the database.

There are many different types of databases available such as Microsoft Access Databases, Oracle Databases, MySQL databases and so on but Microsoft Excel also has a form of a database known as a database list. The form of the list is virtually the same as the other databases as the data is under column headings in rows, but after that common point, the Excel database goes in its own direction. See, to look for specific data within a Microsoft Excel Database or Excel List we do not use the common database language of SQL, we actually use specially written functions. These functions are custom written by you and are known as criteria.

So how do we create an excel database...

Well first off, there is one rule we must always follow and that is one excel database per worksheet. Anymore and you just get yourself into lots of trouble. In fact if you need to have multiple excel databases within your workbook simply put each excel database onto a separate worksheet.

The next thing you must follow is that your database lists first row must contain the heading of the list. That is the first row contains your field names. Plus each of the field names must be 100% unique. You cannot have two field names with the same name or again you will have a list that will not work.

The next issue you need to be concerned with is identifying the field names. Excel databases have a simple rule, the field names or column names must be unique. Now the way you identify them is easy, all you have to do is to ensure the field names are many different data types, format, pattern etc to the rest of the database in your list. Generally what I do is to format my field names in bold to satisfy this requirement.

One of the most important rules you must remember when you create an excel database is that around the row and columns of the fields and data there must be a blank row and column. What this means is that you can still have a heading at the top of the fields, but there must be a blank row between the heading and the fields as well as along the last column as well. The blank row rule also applies to the bottom of the list as well.

When you are entering data into your list, every cell in every record must contain some value even if it is simply blank (a blank value is still considered a value) and each record must contain the same number of fields. If there is no specific data for a field you simply leave it blank and move to the next field.

Ensure that when you are entering data into a field that you don't have spaces before the text or at the end of the text in the field. If you do have spaces, then what will happen is that sorting and search for data in the list will be compromised and you will get unexpected results.

Upper case and low case characters in the field do not affect the searches or sort orders unless you specifically tell the Microsoft Excel application it is an issue. You can also use formulas in a cell if required. Formulas can refer to cells within the Excel Database List or outside of the Excel Database.

Note also that you can edit and format the cells just like any other spreadsheet however the issue you must consider as a priority is that the field names must have a different format to the rest of the data in the database list. It is highly recommended that there be no other formatting in the list except for the field headings. This ensures that there are no miscalculations by the application as to what is a field heading in the excel database and what is not.

Now that you have setup your list in this way following these rules, you are now ready to interrogate the list by applying criteria. The easiest way that you can do this is by using the Form dialog box. To get into the Excel Database form you simply choose the Data menu and then choose Form from the drop down menu.

From the Excel Database Form you can simply choose the Criteria button, type the criteria you have for your data and choose the Find Next button and it will take you to the first record that satisfies the criteria you are searching. Excel Databases are particularly useful for summary data that is where the volume of records you have in your database doesn't exceed 65,536 rows.

If you follow these rules to create an excel database you will find that the functions associated with the excel database list will work in an effective and efficient manner.

Chris Le Roy is the Managing Director of One-on-One Personal Computer Training. He provides a range of services including a daily mailing list that provides Tips on Microsoft Excel as well as he has Microsoft Excel Cheat Sheets to help you remember your functions and your Excel Shortcuts. He also has free excel templates for downloading include an Excel Paystub Template

BINOMDIST, CRITBINOM and NEGBINOMDIST Formulas for Binomials

2011-05-22T00:36:00.000-07:00

A binomial distribution describes the outcome of a multi-step experiment, consisting of

n identical trials, where each trial ends in either a success or a failure and the probability of

a success p does not change from trial to trial. This useful statistical analysis can be performed relatively easily using Microsoft Excel using the Excel BINOMDIST, CRITBINOM and NEGBINOMDIST functions.

Note, however, that when making binomial probability calculations, the trials must also be independent so that success in one trial does not affect the probability of success in another trial. The binomial random variable x is the number of successes observed in n trials.

If samples are not replaced, and therefore the outcome of one trial changes the probability of success in another trial, you need to use the hypergeometric probability distribution Excel function.

Using Excel's BINOMDIST Function

For example, if you flip a coin n times and "heads" is called a success, then the random variable

x would be the number of heads observed in n flips. It could take the values 1,2,3,...,n with different probabilities.

The BINOMDIST function uses the following syntax:

=BINOMDIST(x,n,p,cumulative)

If you want to find the probability of exactly x successes, enter FALSE as the fourth (cumulative) argument. If you want to find the probability of x or fewer successes, enter TRUE as the fourth argument.

For example, if you were to flip a fair coin 20 times and wanted to find the probability of it turning up "heads" exactly 10 times, the function looks like this:

=BINOMDIST(10,20,0.5,FALSE)

The function returns the value 0.176197052. If you wanted to find the probability of getting

10 or fewer heads, you replace the FALSE with TRUE, and the function returns the value 0.588098526.

Using Excel's CRITBINOM Function

The acceptance criterion function, CRITBINOM, is used for quality control of a production process. You use this function to find the maximum number of defective items that a person can find in a lot and still allow acceptance of the lot. Inspectors should accept the lot if they find this number or fewer defective items and reject the lot if they find more defective items.

To determine the acceptance criterion, you need to know the number of items in the lot, the probability of accepting each item, and the producer's allowable risk (alpha) for rejecting an acceptable lot.

The CRITBINOM function uses the following syntax:

=CRITBINOM (trials, probability_s, alpha)

where trials is the number of trials, probability's is the probability of a success on each trial,

and alpha is the criterion value. Probability's and alpha are both between 0 and 1.

Using Excel's NEGBINOMDIST Function

If the number of successes is fixed in a binomial distribution and you want to find the number of trials, use the NEGBINOMDIST function. This function returns the probability that there will be a certain number of failures before the threshold number of successes, given the constant probability of a success.

For example, if you need to find 20 straight 2 by 4s from a stack, and you know the probability that a board in the stack is straight is 0.2 (20%), you can use the NEGBINOMDIST to find that there is about a 2% probability that you will reject 75 boards before finding all 20 straight ones.

The NEGBINOMDIST function uses the following syntax:

=NEGBINOMDIST (number failures, number successes, probability of success)

For this example, the function looks like this:

=negbinomdist (75, 20, 0.2)

About the author: Seattle accountant and author Stephen L. Nelson wrote the MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself incorporation kits that entrepreneurs and investors can use for setting up a Montana limited liability company or a Nebraska limited liability company.

CHIDIST, CHIINV and CHITEST for Chi-Square Calculations

2011-05-20T00:35:00.000-07:00

The chi-square distribution is commonly used to make inferences about a population variance.

If a population follows the normal distribution, you can draw a sample of size N from this distribution and form the sum of the squared standardized scores (chi-square). This random variable chi-square follows the chi-square probability distribution with n degrees of freedom (df ), where n is a positive integer equal to N-1. The degrees of freedom parameter determines the shape of the distribution. With more degrees of freedom, the skew is less.

CHIDIST

The CHIDIST function returns the area in the upper tail of the chi-square distribution. You use the CHIDIST function the same way you would use a chi-square distribution table. The CHIDIST function uses the following syntax:

=CHIDIST (x, df)

For example, if you pull a random sample of 16 from a population and want to find the probability of a sample chi-square value (x) 25 or larger, you would enter:

=CHIDIST (25,15)

The function returns the value 0.049943, meaning that a value of 25 or more should in the long run occur about five times in a hundred.

CHIINV

You can use the CHIINV function to create confidence interval estimates of a population variance. That is, you use the CHIDIST function if you know x and want to find the probability, and you use the CHIINV function if you have a probability and want to find x. For example, if you're creating a product and weigh a sample of 18 units to find a sample variance of 0.36, you may want to construct a 90% confidence interval estimate of the population variance for the product. With a sample size of 18, you have 17 degrees of freedom.

To find the upper limit, enter:

=CHIINV (0.95,17)

To find the lower limit, enter:

=CHIINV (0.05,17)

These formulas return the values 8.67175 and 27.5871. Multiply the sample variance of 0.36 by the degrees of freedom and divide this product by each of the values returned from the CHIINV function to find the lower and upper limits of the confidence interval. You can take the square root of these values to establish interval estimates of the population standard deviation.

CHITEST

The chi-square test is used to test independence of two variables. You can use the chi-square test to determine whether there is a significant difference between observed and expected frequencies. For example, if you want to find out whether soft drink preference differs between male and female drinkers, you can construct a null hypothesis that soft drink preference is independent of the gender of the drinker, and create a worksheet range, or table, of expected results based on a sample of 93 male drinkers and 85 female drinkers. You can then create a table of the results of the actual study findings.

TIP: You can use the Microsoft Excel Fisher's test function instead of the chi-square test for analyzing contingency tables with two rows and two columns. Fisher's test always returns the exact P value, whereas the chi-square test returns only an approximate p value. Definitely avoid the chi-square test when the numbers in the contingency table are very small (in the single digits).

The CHITEST formula uses the following syntax:

=CHITEST (actual range, expected range)

where actual range is the data in the actual sample results table and expected range is the data from the expected results table.

The formula returns the p-value. You reject the null hypothesis if this value is less than your level of significance alpha. So if your level of significance is .05, you would reject it, but not if your level of significance is .025 or .01. The test for independence is a one-tailed test, so a level of significance of .05 corresponds with a 95% confidence level.

About the author: Seattle accountant and author Stephen L. Nelson wrote the MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself business incorporation kits that entrepreneurs and investors can use for setting up a Nevada limited liability company or a New Hampshire limited liability company.

Calculations in Sensitivity Analysis

2011-05-18T00:35:00.000-07:00

One of the finest features in Microsoft Excel is sensitivity analysis using either a table (Excel 2003) or 'What-if' in Excel 2007. Suppose you want to start a cybercafe or a restaurant in a new mall. You have done a study on the footfall and the kind of people who visit the mall. You have also found out about the business atmosphere, security and rent or the outright purchase price. You also know the rates in the market that other businesses are charging, let's say, for surfing the net per hour. You then estimate your capital costs like doing up the cybercafe and the price of the computers. You also use the Excel spreadsheet to estimate and calculate the number of people you'll need to run the show and the amount of salaries you'll have to pay. You have also estimated other variable costs like electricity and phone.

From the above data in the Excel worksheet you can calculate your total monthly or yearly costs. Now based on a certain price that you will charge the customers, number of computers and working hours you can calculate your revenue per month or per year. From the data of revenue and income you can easily calculate the profit. Till now everything was easy to implement in Excel.

Now you decide to find out how your profit can vary if you vary the charge per hour or the number of people who will visit your cybercafe or establishment. Of course, you cannot charge what you want but you can get a good estimate by observing what others are charging and what quality of service and environment they are providing.

Arranging all your data properly, click on 'Data' in the ribbon in Microsoft Office 2007 or 'Data' in the menu bar in Excel 2003. In Excel 2007 select 'What-if' analysis and finally 'Data Table...'. In the popup window in the 'Row input cell' type the data that you have input horizontally next to the profit and in the 'column input cell' write down the price and vary it it by 1% 0r 2% so that that Excel can perform an analysis for, say., $0.5 per hour charge for a cybercafe to $1.5 per hour. The horizontal values can be the number of people per hour or month or year that will visit the shop and keep on varying the values by a certain estimated percentage. Click 'OK' and you can see how your profit varies with the number of customers and the price you charge. This is also known as a two variable table because you calculated the changes in your profit based on two parameters - price and number of customers.

You can also do a single variable sensitivity analysis by selecting only one parameter.

You can use the 'Data' and 'Table' feature in Excel 2003 to implement the same analysis.

The practical implementation of the above sensitivity analysis in the form of a training video can be found in the link below.

Dr. Dinesh K Takyar is a corporate trainer in Microsoft Excel.

http://www.familycomputerclub.com

CORREL, PEARSON and RSQ Formulas for Correlation

2011-05-16T00:34:00.001-07:00

Excel provides useful statistical functions for measuring correlation between two variables. As a reminder, the benefit of using a correlation coefficient to measure the relationship between two variables as opposed to using covariance is that the unit of measurement doesn't matter.

But a caution: Remember that correlation does not show causation. That is, you could easily show that as the number of ice cream cones consumed increases during a year, so does the number of drownings. But this does not mean that eating ice cream causes people to drown-more likely, these variables are both independently related to another variable-that of temperatures. Correlation is symmetrical, so you get the same coefficient if you switch the variables. Don't calculate a correlation coefficient if you manipulated one of the variables. Use linear regression instead.

CORREL

You use the CORREL function in Excel to determine whether two data sets are related, and if so, how strongly. The correlation coefficient ranges from +1, indicating a perfect positive linear relationship, to -1, indicating a perfectly negative linear relationship. To calculate a correlation coefficient for a sample, Excel uses the covariance of the samples and the standard deviations of each sample. To use the CORREL function in Excel, just select the two sets of data to use as the arguments and use the following syntax:

=CORREL(data set 1,data set 2)

For example, if you have a set of preliminary test scores for a sample of employees in column

A and a set of performance feedback scores in column B, as shown in Figure 4-6, and

you want to find out whether they're related and if so, how strongly, you can use Excel to

find the correlation coefficient for the samples.

The function returns the value 0.87, indicating that the sets are positively related (as the value

of one goes up, the value of the other also increases), but the relationship isn't perfect.

PEARSON

The Pearson product moment correlation coefficient function, PEARSON, uses a different

equation for calculating the correlation coefficient. This formula doesn't require the

computation of each deviation from the mean. Still, the correlation coefficient ranges from

+1, indicating a perfect positive linear relationship, to -1, indicating a perfectly negative linear

relationship. The PEARSON function uses the following syntax:

=PEARSON(data set 1,data set 2)

Using the PEARSON function on the data shown in Figure 4-6 to compute the correlation coefficient returns the same value as the CORREL function does.

RSQ

The RSQ function calculates the square of the Pearson product moment correlation coefficient through data points in the data sets. You can interpret the r-squared value as the proportion of the variance in y attributable to the variance in x. The RSQ function uses the following syntax:

=RSQ(data set 1,data set 2)

About the author: Seattle CPA Stephen L. Nelson wrote the bestselling book, MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself incorporation kits that businesses and investors can use for setting up an Nebraska incorporation or an Nevada incorporation.

How to Build IF Formulas

2011-05-12T05:00:00.000-07:00

The IF function in Microsoft Excel seems to really cause many people a lot of heartache and grief, but all in all it is actually really not that difficult of a tool to use. The essential role of the IF function is to test whether a condition is either True or False and if the condition is True it will return one value and if it is False it will return an alternate value.

Let us look at an example -

Let us say you have two values. In one cell you have a value of 65 and in the other cell you have a value of 23. What the IF function allows us to do is to do a test and lets say we want to see if the value of 65 is greater than 23. When the numbers are plugged into the If statement, the result will be that 65 is greater than 23 and it will return something from the True return area.

So how do we create an if statement...

The secret to the IF function is really in its syntax. The syntax for the IF function is as follows -

IF(Logical Test, True Result, False Result)

What we can do is to now create the example we did earlier so begin by simply opening a Microsoft Excel Work sheet. In cell address A1 enter the text IF Test Condition. In cell A2 enter the text - Numeric Value 1, in A3 enter the text - Numeric Value 2, and in cell A5 enter the text Which Value is Larger?

Now we need to put a few numbers to test into our Excel Spreadsheet, so in cell address B2 enter the value of 65 and in cell address B3 enter the value of 23. In cell B5 we need to create our formula. If we use the example above we want to find out if the value in B2 is greater than the value in B3. If the condition is true we want to return the text - Numeric Value 1 and if the condition is false we want to return the text - Numeric Value 2.

The structure of the IF Function to do the test will look like this -

= IF(B2>B3, "Numeric Value 1","Numeric Value 2")

What you will notice in the function above is that the text values are encapsulated in double quotes. Whenever working with text in Formulas the text must always have double quotes around it. If you type in the formula above into cell address B5 you should see the resulting text - Numeric Value 1. If you change the value in B2 now to 12, the automatic result in B5 should be - Numeric Value 2.

The IF function is a really diverse tool and it allows you to test an unlimited number of conditions to get the required outcome, you can even nest IF functions within IF functions but the bottom line is this, for every IF function the syntax should always look the same as above.

Chris Le Roy has available Microsoft Excel Shortcuts to help you with Microsoft Excel. To learn more about the IF Function simply check our Chris's correspondence course where you can earn yourself Microsoft Excel Certificates issued by his company without even leaving home - Microsoft Excel Spreadsheet Training

Tips on Microsoft Excel are also Available.

Mathematical Varience with Formulas

2011-05-09T04:56:00.000-07:00

Excel provides useful statistical functions for finding the variance of a data set. In general, these variance functions retrieve a set of values stored in a worksheet range and then make the expected calculation.

A variance, just to remind readers, is a common measure of describing the spread of observations in a distribution. A variance is related to another statistical measure, the standard deviation. A variance is equal to the square of the standard deviation.

Variance of a Sample with the VAR Function

If the data set you're working with is a sample and you do not want to include logical values or text from the set in the calculation, you use the VAR function. For example, if you're using a new production process that is supposed to increase productivity and have a series of data for the numbers of parts produced each day, you can find the sample variance. The VAR function uses the following syntax:

=VAR (data set range)

Variance of a Sample with the VARA Function

If the data set is a sample but you want to include logical values or text in the calculation, you use the VARA function. Excel counts cells containing the logical value TRUE as 1 and cells containing text or FALSE 0. The VARA function uses the following syntax:

=VARA (data set range)

Variance of a Population Sample with the VARP Function

If the data set you're working with is a population and you do not want to include logical values or text from the set in the calculation, you use the VARP function. The VARP function uses the following syntax:

=VARP (data set range)

Variance of a Population with the VARPA Function

If the data set is a population but you want to include logical values or text in the calculation, you use the VARPA function. The VARPA function uses the following syntax:

=VARPA (data set range)

A Final Note About the Data Set Range Argument

One other note: You can include multiple worksheet ranges as your data set range argument when using the variance functions. For example, if you were calculating the variance of a sample using the VAR function and had your data stored in several different worksheet ranges, your VAR function might look like this:

=VAR(B1:B:10,B101:B110,B200)

Small business incorporation and limited liability corporation CPA Stephen L. Nelson has written do-it-yourself limited liability kits for all fifty states. He holds an MBA and MS in tax.

Linear Regression Formulas

2011-04-24T01:13:00.000-07:00

You use Excel's linear regression functions to find a linear equation that best describes a data set.

Excel uses the sum of least squares method to find the straight line of best fit. People often

try to predict future amounts by assuming linear growth and extending the line forward in

time. For example, if you have a series of sales data for 9 months and want to predict the

sales in the 10th month, you can use Excel's linear regression functions to find the slope and

y-intercept (the point on the y-axis where the line crosses) of the line that best fits the data.

Some Background Info on Linear Regression

To use the linear regression functions, it helps to remember the equation for a line:

y=mx+b

where y is the dependent variable, m the slope, x the independent variable, and b the

y-intercept. If there are multiple ranges of x values, the equation looks like this:

y=m1 x1+m2x2+.mnxn+b

NOTE To visualize and experiment with linear regression, visit the interactive web page at

[http://www.math.csusb.edu/faculty/stanton/m262/regress/regress.html]. Click the

graph area to add data points (x,y) to the graph. The applet draws the straight line

that best fits the points you add, adjusting the line for the new data points you add.

Using the FORECAST Function

The FORECAST function predicts a future y-value for the x-value you specify using existing

x and y values. The FORECAST function uses the following syntax:

=FORECAST(x, known ys, known xs)

where x is the x-value for which you want to predict a y-value.

Using the INTERCEPT Function

If you have existing x and y values, Excel can find the straight line that best fits the data and then calculate the point at which the line intersects the y-axis, in other words, the value of b in the "y=mx+b" equation. The y-intercept is useful when you want to know the value of the dependent variable when the independent variable equals 0.

NOTE: The INTERCEPT function returns the same value as the FORECAST function if you enter 0 for x in the FORECAST function.

The INTERCEPT function uses the following syntax:

=INTERCEPT (known ys, known xs)

Using the LINEST Function

The LINEST function returns the value of m and b given at least one set of known ys and known xs. The LINEST function has the following syntax:

=LINEST (known ys, known xs, constant, statistics)

where known ys is the array of y values you already know, known xs is the array of x values you may already know. If you leave out the known xs, they are assumed to be 1, 2, 3,...n. If constant is set to FALSE, b is assumed to be 0. If statistics is set to TRUE, the LINEST function also returns the standard error for each data point.

NOTE: If the known ys are in a single column or row, then Excel considers each column of

known xs to be a separate variable.

NOTE: The array known xs can include multiple sets of variables. If you use only one set, then known ys and known xs can be ranges of any shape, as long as they have equal dimensions. If you use more than one variable, then the known ys array must be either a single column or a single row. If you don't enter known xs, Excel assumes this array is the same size as the known ys array.

Using the SLOPE Function

Use the SLOPE function to find the slope (m) of the linear regression line from the known x and known y data sets. The slope is the change in y over the change in x for any two points on the line. The SLOPE function in Excel uses the following syntax:

=SLOPE (known ys, known xs)

A positive (upwards) slope means that the independent variable (such as the number of salespeople) has a positive effect on a dependent variable (such as sales). A negative (downwards) slope means that the independent variable has a negative effect on the dependent variable. The steeper the slope, the more effect the independent variable has on the dependent variable.

Using the STEYX Function

Use the STEYX function to find the standard error of the predicted y-value for each individual x in the regression. The STEYX function uses the following syntax:

=STEYX (known ys, known xs)

Using the TREND Function

Use the TREND function to find values along a linear trend. Specify an array of new xs and the TREND function uses the method of least squares to fit a straight line to the known x and y data sets and return the y-values along the line for the new array. If constant is set to FALSE, the "b" in the y=mx+b equation is set to zero. The TREND function uses the following syntax:

=TREND (known ys, known xs, new xs, constant)

Stephen L. Nelson, CPA wrote the MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself small business incorporation kits that businesses and investors can use for setting up a Michigan S Corporation or a Minnesota S Corporation

Origins, Programmability and Popularity of Excel

2011-04-20T01:06:00.000-07:00

The first Windows version for Excel - Excel 2.0 was released in 1987 and since that time, its popularity has only increased. The spreadsheet and data tabulating software category was previously dominated by software like Lotus 1-2-3 which were DOS based. Microsoft Excel was probably the first GUI based spreadsheet application that revolutionized the way people look at such software. Excel had many advantages over other similar products. Some of the important ones are listed below:

For the first time, it became easy to calculate, tabulate and organize data in a graphically pleasing way. The impact that these new advancements had on report generation and presentation is well known and needs no introduction.

At the same time, highly complex algorithms could also be applied to the same data for data mining and statistical analysis means. These algorithms could range from simple aggregate functions like Average, Min, Max, etc to complex statistical functions like normal distribution plotting, Chi-square values etc.

Tight and seamless integration with the office suite: Starting from Excel 5.0, programs like Microsoft Word and Microsoft Access could basically share data with each other. In layman's terms, what this means is that an Excel table could directly by inserted in a Word document and similarly, an Access database file could be used to provide data to an Excel table.

Programmability: Visual Basic for Excel - a programming language based on Visual Basic that was widely used in developing custom functions for Excel. This language was powerful enough to make system level calls and develop self sufficient applications that ran on top of the Excel runtime environment.

Support for old formats: Excel supported importing of old format files like Lotus 1-2-3 worksheets, Dbase files etc, which made user migration very easy.

The main reason for the rise of popularity for Microsoft Excel was the fact that older spreadsheet-based programs like Lotus 1-2-3 were not Windows based and by the time a GUI version of the same was launched, Excel had already dominated the market. Another reason was that it was shipped with the Microsoft Office Suite and was tightly integrated with it.

Microsoft Excel today is an indispensable product and can be found in just about every computer that runs Windows. Alternatives for Microsoft Office Suite like OpenOffice.org (a free an open source product) exist, but they have yet to make a dent in the Microsoft Office Suite's market share.

New iterations and updates of the Office Suite are continuously released by Microsoft to address security, stability and performance issues as discovered. To sum up, Microsoft Excel will continue to dominate the spreadsheet application category for a long time to come.

Computer Training Solutions provide excel training in London and excel course

Advanced IF-THEN Formulas

2011-04-15T02:12:00.000-07:00

One of the basic statements for financial modeling is the IF-THEN function in Excel. While this may not be considered as robust as other approaches when doing actual computer programming (C++, Java, VB), in Excel it is rather straightforward and ubiquitous.

There are a couple of things to remember when using the IF-THEN approach:

* For basic results like "if this cell equals dog put a bone in the cell next to it" or "if the cell on my right equals rain display an umbrella sign in the cell below" or something like that. IF-THEN works well for binary situations, and is a method that the basic financial modeler can use and understand readily easily. The beauty of the function at its most basic level is the simplicity.

* IF-THEN can also be used for non-binary situations, meaning that one can "nest" several sub IF-THEN statements within the cell. For example, let's assume that you had four triggering events: apple, orange, grape and banana. Depending upon the word that was in a cell, there would be one of four results. This would look like:

=IF(A1="Apple","Watch for worm",IF(A1="Orange","Citrus",IF(A1="Grape","Soon to be a raisin",IF(A1="Banana","Don't slip on the peel","Do nothing")))).

You will notice that the last bit of this text includes "do nothing", which captures any text that is not one of the specified amounts you are seeking. If there were only four options (that somehow the input cell was blocked to prevent anything other than those four choices), the code would look like this:

=IF(A1="Apple","Watch for worm",IF(A1="Orange","Citrus",IF(A1="Grape","Soon to be a raisin","Don't slip on the peel"))).

By definition, only four choices will yield three IFs - think about the fourth IF as silent to help you see why this is the case.

* The financial modeler can also link IF-THEN statements to apply a mathematical operator to two or more statements in the cell. Thus, you could create a formula like =IF(A1="Pie",1,0)+IF(B1="Soup",2,0)+IF(C1="Water",3,0). You can do this with any operator and is useful if there are discrete components to the model you are developing. As you ultimately get further into advanced modeling, this can be used for triggers, or cells that create an influx of additional information when turned to a true state. Further discussion on that will occur as we move to intermediate level issues, but just remember that there is flexibility in the IF-THEN statement from this particular perspective.

* One shortcoming of the IF-THEN statement usage is that you can only have up to seven nested statements EVER in any function in Excel. For examples, the following is a maxed out version of the IF-THEN statement:

=IF(C5=1,1,IF(C5=2,2,IF(C5=3,3,IF(C5=4,4,IF(C5=5,5,IF(C5=6,6,IF(C5=7,7,IF(C5=8,8,"No")))))))) - if you try to add another nested IF statement, you will get an error message. Part of the rationale behind that is because evaluating the IF-THEN in the standard Excel environment (as well as computer language in general) takes some time to evaluate. Granted, it is not a lot of time when thinking about a machine calculating something faster than us, but each of the evaluation steps requires computing speed and memory. If you can imagine a nested 20-step IF-THEN statement in your mind and pretend that you must evaluate that statement in order until the last part of it, you will begin to see why there needs to be extra time for the computer to do analyze it. Thus, seven is the cutoff point, but in reality, you should never need that many nested IF-THEN statements anyway.

In summary, the IF-THEN statement can be a very useful, simple approach to binary and certain non-binary situations. It is not perfect for larger database inquiries or very large spreadsheets wherein the answer to the IF-THEN drives the model, but it can suffice for everyday simple financial modeling applications, and it easy to implement and interpret.

Russ Steward has more than fifteen years of experience in investment banking and private equity, and has developed hundreds of financial models and analyses in Excel. For more information, please visit http://www.makefinancialmodels.blogspot.com

Excel versus Openoffice Spreadsheets

2011-04-11T02:08:00.000-07:00

Microsoft Excel, the spreadsheet software developed and commercialized by Microsoft, is part of the Office Suite and it is available both for Macintosh systems and Windows. Originally designed to work on Macintosh systems, Excel had been ported to MS/DOS in 1987 due to the slow porting of Lotus 1-2-3 to the Microsoft operating system and since then it has been improved in its features, dominating the commercial spreadsheet market.

Excel can be installed both as a stand-alone application and as a part of Office Suite, offering in the latter case full integration with Word, PowerPoint and Publisher. Excel offers basic functions for data manipulation, like arithmetic operations and formulas, as well as embedded statistical functions, charts, graphs and histograms. Macro functions, designed to handle more complex data processing, make use of Visual Basic for Applications, a subset of the famous scripting language developed by Microsoft. Such a offer of features conveniently answer to statistical, engineering and financial needs, making Microsoft Office a first pick in the great variety of spreadsheet applications today available in the market.

What makes competitors a viable alternative is the pricing policy of Microsoft. Microsoft won't let you buy Excel as a stand-alone application. The software is sold as a part of the Microsoft Office Suite, offered at 149$ per license in the Home and Student version, 279$ for Home and Business version and 399$ for the Professional Suite. For people finding such prices impossible to afford it is necessary to move to cheaper reliable solutions, like OpenOffice.org suite, which features Calc as spreadsheet software.

OpenOffice.org is a complete suite, offering word processing, presentations and publishing as well as his famous spreadsheet application, Calc. Developed by Oracle, it can be freely downloaded and installed on Windows, Linux and MAC OS X, as well as on other operating systems on which Office is not able to run. While the huge different in price could make somebody think that Calc is not a viable alternative to Office, Oracle's spreadsheet features all the functionalities provided by Office: basic arithmetic operations, statistics, histograms and graphs, as well as being able to read and write in Microsoft formats like .xls, .doc and .ppt. Quick answers to your problems will be provided by the documentation provided by developers and users and it's possible to get direct and personal help from the Free Software/Open Source community, which is directly active in the development of the software.

As we just said, there are viable alternatives to reliable but expensive applications like Excel, keeping in mind that moving to a new platform is an investment in time, and hence, money. OpenOffice.org offers a good and cheap alternative, especially after the introduction of full compatibility with Microsoft most used formats. In business world, no matter how small your business is, being able to edit a.doc file makes the difference.

Computer Training Solutions provide Excel Courses and excel training courses

How to Calculate Postcode and Zipcode Radii

2011-04-03T00:56:00.000-07:00

Microsoft Excel "custom functions" can be used to carry out many tasks related to address and location-based information, such as importing demographic data, checking address accuracy, even calculating driving time and performing route optimization. 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 zips 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 can do the work for you.

Let's say you are evaluating a possible store location in zip 07470 and need a list of all zips within a 10 mile radius. To use a function named RADIUS, simply input a formula in a worksheet cell like this: "= RADIUS (ZipCode, RadiusDistance)". For this case the formula is "= RADIUS ("07470", 10)", and the worksheet will display all results within 10 miles of 07470.

The formula can be tailored to output the data as a text string 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 calculated distance and sort these by closeness to the target zip. Additional options can be added to have the distance returned in kilometers or miles.

The custom function automates the process of finding all zips that fall within the given radius by searching a local 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 these radius calculations, such as calculating the distance between two given zip codes. The custom function formula in this case would be something like "= DISTANCE (ZipCode1, ZipCode2)". The calculated distance can be "as the crow flies" or can be estimates of actual driving distance or time.

A radius custom function is an excellent example showing how Microsoft Excel functions can be used to automate complicated tasks, all within a simple spreadsheet formula. From calculating driving distance and time between addresses, to identifying zips within a specified radius, custom functions are a very useful approach to analyzing address and location-based information in Excel.

Please see the YouTube video Find Zip Codes in a Radius for more information about using Excel custom functions. The author of this article, Betty Hughes, helped develop CDXZipStream, an Excel add-in that provides zip code data, demographics by zip code, driving route optimization, zip code radius analysis, geocoding and more using custom functions. A free 30-day trial is available as a download from our website, as well as example spreadsheets and tutorials showing how to use custom functions in a variety of applications.

Excel FIND and SEARCH Formulas

2011-03-31T00:53:00.000-07:00

There are two very similar functions in Excel to look for data inside of cells matching parameters that you dictate: SEARCH and FIND. There are so similar, in fact, that one wonders why have two separate functions that perform virtually the identical results and are identical in the construct of the formula. This article will discuss he one, basic difference.

SEARCH Introduction

The SEARCH function is a way to find a character or string within another cell, and it will return the value associated with the starting place. In other words, if you are trying to figure out where a character is within the cell that contains a word, sentence or other type of information, you could use the SEARCH function. The format for this function is:

=SEARCH("find_text","within_text",start_num).

If, for example, the word "alphabet" was in cell C2, and your model needed the location of the letter "a" in that cell, you would use the formula =SEARCH("a",C2,1), and the result would be 1. To continue this simplistic example, if you were seeking the location of "b" in the word, the formula would be =SEARCH("b",C2,1), and the result would be 6. You can also use search on strings of characters. If, for example, cell F2 contains 1023-#555-A123, the formula =SEARCH("A12",F2,1) would yield the 11 as an answer.

FIND Introduction

The FIND function is another way to find a character or string within another cell, and it will return the value associated with the starting place, just like the SEARCH function. The format for this function is:

=FIND("find_text","within_text",start_num).

Using the same example as before, the location of the letter "a" in cell C2 would be discovered using =FIND("a",C2,1), and the result would be 1. Looking for "b" in cell C2 would be accomplished be =FIND("b",C2,1), resulting in the number 6. Finally, continuing on the similarity path, if cell F2 contains 1023-#555-A123 (as before), the formula =FIND("A12",F2,1) would yield the 11 as an answer. As you can see, up to this point, both methods would give you the same results.

Note: You probably quickly recognized that there are two a's in the word located in cell C2. By stating the starting point in each of the formulas as 1, we will pick up the first instance of the letter "a". If we needed to choose the next instance, we could merely have the "start_num" part of the formula to be 2, thus skipping the first instance of the letter and resulting in an answer of 5.

Main Differences

The main difference between the SEARCH function and the FIND function is that FIND is case sensitive and SEARCH is not. Thus, if you used the formula =SEARCH("A",C2,1) (note the capital "A"), the result would still be 1, as in the case before. If you were to use the formula =FIND("A",C2,1), you would get #VALUE!. FIND is case sensitive and there is no "A" in the word "alphabet".

Another difference is that SEARCH allows for the use of wildcards whereas FIND does not. In this context, a question mark will look for an exact phrase or series of characters in a cell, and an asterisk will look for the beginning of the series of characters right before the asterisk. For example, the formula =SEARCH("a?p",C2,1) in our alphabet example would yield an answer of 1, as it is looking for an exact grouping of the letter "a" with anything next to it with a "p" immediately after. As this is in the beginning of the word, the value returned is 1. Continuing with the alphabet example, the formula =SEARCH("h*t",C2,1) would yield a value of 4. In this instance, the wildcard "*" can represent any number of characters in between the "h" and the "t" as long as there is a string beginning and ending with the two letters you use in the formula. If the formula was =SEARCH("h*q",C2,1), you would get #VALUE!.

In short, these two formulas are very similar, and unless you need confirmation of an exact character or string of characters, you would likely err on the side of using SEARCH. Instances where this may not be the case might involve searches involving specific SKUs or names of employees. In my experience, SEARCH has been more helpful in specific financial modeling exercises, but it is helpful to understand the differences in usage and results as you work through your own modeling projects.

Using Excel for Interest Rates

2011-03-19T01:24:00.000-07:00

The Rate function calculates the interest rate implicit in a set of loan or investment terms

given the number of periods (months, quarters, years or whatever), the payment per period, the present value, the future value, and, optionally, the type-of-annuity switch, and also optionally, an interest-rate guess.

If you set the type-of-annuity switch to 1, Excel assumes payments occur at the beginning

of the period, following the annuity due convention. If you set the annuity switch to 0 or

you omit the argument, Excel assumes payments occur at the end of the period following

the ordinary annuity convention.

The function uses the following syntax:

RATE (nper, pmt, pv, fv, type, guess)

As one example, suppose you want to calculate the implicit interest rate on a car lease for a $20,000 car that requires five years of $250-a-month payments (occurring as an annuity due) and also a

$15,000 balloon payment. To do this, assuming you want to start with a guess of 10%, you

can use the following formula:

=RATE(5*12,-250,20000,-15000,1)

The function returns the value .95%, which is a monthly interest rate of just less than 1%.

If you annualize this monthly rate by multiplying it by 12, you get an equivalent annual

interest rate of 11.41%.

As another example, suppose you want to calculate the implicit interest rate on a $300,000 real estate mortgage that requires thirty years of $2000-a-month payments (occurring as an ordinary annuity) but (thankfully) no balloon payment. To do this, assuming you want to start with a guess of 10%, you can use the following formula:

=RATE(30*12,-2000,300000)

The function returns the value .59%, which is a monthly interest rate of slightly more than half a percent.

If you annualize this monthly rate by multiplying it by 12, you get an equivalent annual

interest rate of 7.0203%.

A final point: Excel solves the RATE function iteratively starting with the guess argument you provide.

(If you don't provide this optional argument, Excel uses 10%.) If Excel can't solve the RATE argument within 20 attempts, it returns the #NUM! error. You can try a different guess argument, which may help because you're telling Excel to begin its search from a different (hopefully closer) starting point.

Stephen L. Nelson is a small business tax CPA specializing in S corporations and the author of many bestselling books including the MBA's Guide to Microsoft Excel from which this article is adapted. Nelson also edits the Forming an S corp online, the Incorporating a small business and the forming an LLC web sites.

Microsoft Excel for Accountants

2011-03-07T00:15:00.000-08:00

The ever changing technology brought brand new ways of completing any kind of tasks rapidly. It also helps us to produce data and maintain files in a clear manner. The field of accountancy is not an exceptional case. It has got tremendous improvements with the help of the advanced methodology. In this article, we are going to discuss about the role of the excel spreadsheet in accountancy.

Spreadsheets generally save a lot of your time no matter if you use it to track expenses, cash flow, project tax or inventory. It will be helpful in analyzing lease or purchase and capital requirements. The formula in Excel helps you a lot to maintain a trouble free reports of all the transaction details.

1. People from various departments have to handle the same document file. The sales executive has to enter the sales he made and forward it to his Manager. The Manager will forward the same to the bookkeeper. We have several option to share the document and compare it using the Microsoft Excel.

2. It has got in build templates which will be useful for us. It makes our files to be dynamic and smart.

3. There is an option to convert the template as smart document with the help of the excel. Let say the annual employee expense report is based on an Excel template in your company. When you convert this to be a smart document, you will be able to connected this to a database which automatically fill some required information. When you are done with this, you can routed to the concerned person who is in charge of the next step.

4. Using smart documents you can send email messages through MS outlook without starting outlook and without leaving the worksheet.

5. Document workspace is the wonderful option provided by the excel 2003. It is mainly useful to collaborate the documents with other people on your staff. It is the process of co-authoring, editing and reviewing documents.

6. There are lots of shortcuts available in Microsoft Excel. Few of those are listed out below:

Excel Shortcuts:

AutoFilter: It is helpful in handling large spreadsheets easily. You can activate filter option to view or edit a particular set of data. It will not cause any changes on the arrangements. It just hides the unwanted data and shows the data you have filtered. Once you remove or deactivate the filter, this option will be disabled and you will be able to see all the data.

Consolidating data: In Excel, we have an option of importing worksheet or data from another file. This is a quick process and you will not miss any data as you are tend to miss out data while copying and pasting.

Locating discrepancies: You cannot obviously go through the data line by line in order to locate the discrepancies. But in Excel, it automatically identifies the inconsistencies with the little advanced settings.

Also Excel 2007 provides wonderful invoice templates. We can edit the template according to your details and you can send a concise invoice to your clients.

Above all, you can format the excel using formula according to your requirements. This would reduce the considerable amount of time and helps you to have a flawless data. Therefore, excel plays a vital role in accountancy.

Mary is a CPA. She is executing accounting outsourcing projects. Integra outsourcing provides online accounting services for the companies all over the world. Please visit, http://www.integraoutsourcing.co.uk for more details.

Uses of Excel Statistics Tools

2011-02-28T09:39:00.000-08:00

Microsoft Excel statistics have a plethora of uses, limited only by the analyst's imagination, data, and job description. What are these uses and how do you choose the right Excel toolset for maximum utility?

Let's say you work at BP and you're interested in the relationship between the depth of oil wells in the Caribbean with the frequency of oil rig problems. You have several thousand monthly data points of disaster event frequency data and the average depth of each well from the ocean surface to the sea floor, and from the sea floor to the oil pool. You need to quickly generate a probability distribution and estimates of the likelihood of future oil rig disaster events for a board meeting in 1 hour. Several Microsoft Excel statistics tools can generate this analysis in 15 minutes or less. Too bad BP didn't know!

Another example: You hold a portfolio of corporate bonds rated triple-A, Baa, and C in a customer account which has generated significant paper profits in the last year. Your customer asks you whether he should sell this portfolio, or whether he should hold it and buy protection with credit derivatives, and needs to know the answer by the end of the day. You have access to extensive data on historical default probabilities of different bonds with different ratings, as well as historical time series of credit spreads, CDS prices, and equity prices. Using this data and a Microsoft Excel statistics library, you are able to answer with 95% confidence, that based on current market conditions, the length of the bond price runup, and the current economic cycle, credit default spreads on speculative grade bonds are likely to increase by at least 15% in the next 6 months. You recommend selling the C-rated bonds, buying index CDS on the High Yield index, reducing the portfolio's Baa exposure gradually, and holding the triple-A rated bonds.

A final example might be cyclical long term weather patterns to project the price of a basket of food commodities including wheat, corn, beef, soybeans, and orange juice. By importing national weather pattern data from different geographic locations and inflation-adjusted commodity prices into Excel and running a multivariate regression analysis, you are able to ascertain the predictive quality of weather on commodity prices during different time periods. By comparing these results with a similar exercise using geographic population density, lifespan, and education levels to predict commodity prices, you deduce a further potential relationship. Such complex analyses can be easily computed using Excel and statistical tools.

As shown by these examples, the uses of Microsoft Excel statistics capabilities are never-ending.

To learn more about Microsoft Excel statistics capabilities and a value-priced analysis tool, click here http://www.financial-edu.com/excel-random-number-generator-and-statistics-set-2.php.

Tips for Retrieving an Excel Password

2010-02-20T03:21:00.000-08:00

Computers can be reliable and effective but they can also be extremely frustrating. Most people use Microsoft Office to create documents. It's one of the most popular office suites in the world and for good reason, as it comes with some powerful interrelated desktop applications. That includes Word, Excel, Outlook, PowerPoint, Publisher and other desktop applications as well. Most of us use passwords to protect the valuable documents that we have created using Office, but what happens when you realize you've forgotten an Excel password?

If you ever realized that you forgot an Excel password, it can be easy to get stressed.

There is all your most important information, sitting in front of you on a computer but you're unable to access it because you've forgot Excel password. There is nothing more frustrating than that unless of course you have deadlines to meet and that makes things even worse.

At least anyone who has forgot Excel password and those who worry about doing it can find some sort of relief. That's because there is the Office Password Recovery Pro software, which will retrieve all of your passwords in a matter of minutes. This program is designed for people who have forgotten an Excel password. It will recover lost or forgotten passwords, whether they're saved in Word, Outlook or any other Office application.

If you've forgotten an Excel password you can download the software and start using it right away. For those who are a bit timid to go forth with any download without checking it out better first, there is the option of downloading a trial version. This trial version allows users to fiddle around with the different features and get a better idea of all the software has to offer. For anyone who's forgot Excel password, it's sure to bring a sigh of relief.

This software works on all versions of Microsoft Office up to 2003 and is truly the best solution available today for forgotten or lost passwords. It actually does retrieve passwords and supports autosaves. With purchase of the software program, free updates and technical support are offered for a full year, which are some pretty impressive offers.

If you've ever needed to unprotect an Excel password in the past or are just sick of having to try and keep track of all your different passwords, this software will be one of the best investments you ever make. It can even break through any locked cells and shared protection passwords. This is high-tech software that features an easy to use interface, so you really get the best of both worlds.

Mark D Harrison

A life long interest in technology has led to setting up http://www.EasyPasswordFix.com to help people recover passwords from Microsoft Office applications such as Excel, Word, Outlook and PowerPoint.

Use the AutoCalculator in Excel

2010-01-19T03:24:00.000-08:00

Use this if you need to calculate a sum based on a column or a row of numbers and you don't want to type in a funtion.

* Select the figures you need in your spreadsheet.

* Look down at the status bar. There, you'll see the sum of the selected cells.

* If you need to glance at additional calculations, such as average or count, just right-click on the sum calculation and selection the one you need.

Customise Your Autofills

2007-12-19T04:47:00.000-08:00

If you use the same list over and over in different worksheets, you might want to add it to your AutoFill list – this will save you heaps of time in future.

* Highlight your list,

* click on Tools,

* Options…

* and select the Custom Lists tab.

* Click on Import,

* then OK.

Use The Formula Browser

2007-12-19T04:45:00.000-08:00

* Select a cell and,

* click on the Paste Function button on the main toolbar.

* Pick the function you require from the list box and,

* click on OK.

* Now highlight the cells on which you want the target to perform the function and,

* click on OK

Access Help For Lotus Users

2007-12-19T04:42:00.000-08:00

If you’ve converted from Lotus 1-2-3 and find Excel confusing, you can access help functions specific to your situation by...

* clicking on Help,

* Lotus 1-2-3 Help...

Use Template Worksheets

2007-12-18T07:36:00.000-08:00

Templates can save you considerable
time when you’re setting up a new worksheet.

* Click on File,

* New…,

* select the Spreadsheet Solutions tab and,

* choose a template from the list.

Hide Your Data From Prying Eyes

2007-12-18T07:34:00.000-08:00

If you want to hide from view any
sensitive data,

* highlight the relevant
cell and,

* click on Format,

* Cells…

* Click on the Numbers tab,

* select Custom from the Category: list,

* double-click on the Type: input box and,

* enter ;;;.

* Undo the operation to make your data visible again.

Fit Wide Tables To The Page Width

2007-12-18T07:31:00.000-08:00

To make your tables fit neatly on the
page,

* click on File,

* Page Setup…,

* select the Page tab,

*click on the Fit to: radio button and,

* pick 1 page wide.

* Click on the tall box and,

* press [Delete], leaving the box empty.

Remove Hyperlinks From Your Work

2007-12-16T12:51:00.000-08:00

If Excel has already converted your
written URL into a hyperlink, you can cancel it by...

* right-clicking on the offending address and,

* selecting Hyperlink, Remove Hyperlink from the menu that pops up.

Calculate Running Totals

2007-12-16T12:49:00.000-08:00

* Enter the numbers to be added in column A, say A1 to A5,

* then enter =SUM($A$1:A1) into column B.

* Highlight the cells beside the ones with numbers in,

* and go to Edit, Fill, Down.

This places the running total of the figures in A1 to A5 in the adjacent column.

Enter URLs As Text, Not Hyperlinks

2007-12-16T12:47:00.000-08:00

To prevent Excel from converting
written Internet addresses into hyperlinks...

* add an apostrophe to the beginning of the address, for example
‘www.futurenet.com.

Calculate Time Between Dates

2007-12-16T12:45:00.000-08:00

* Enter in a cell the formula A1, where A1 is the earlier date, and

* A2 the later one.

* Don’t forget to convert the target cell to number format – do
this by highlighting the cell,

* clicking on Format, Cells…,

* picking on the Number tab and

* selecting Number from the Category: list.

Convert Rows to Columns

2007-12-16T12:03:00.000-08:00

You can convert rows to columns (and columns to rows) by:

* highlighting the cells you want to switch around,

* clicking on Edit,

* Copy,

* selecting a new cell

* and then going to Edit, Paste Special…

* Finally, place a tick in the Transpose box on the dialog box and

* click on OK.