[ Pobierz całość w formacie PDF ]
.Or, you may never discover theproblem.5594800-x Ch21.F 8/27/01 11:59 AM Page 560560 Part V: Miscellaneous Formula TechniquesResearch on Spreadsheet ErrorsUsing a spreadsheet can be hazardous to your company s bottom line.It s far too easyto simply assume that your spreadsheet produces accurate results.If you use theresults of a spreadsheet to make a major decision, it s especially important to makesure that the formulas return accurate and meaningful results.Researchers have conducted quite a few studies that deal with spreadsheet errors.Generally, these studies have found that between 20 and 40 percent of allspreadsheets contain some type of error.If this type of research interests you, I urgeyou to check out the Spreadsheet Research (SSR) Web site maintained by Ray Pankoof the University of Hawaii.The URL is:http://panko.cba.hawaii.edu/ssr/Formula Problems and SolutionsFormula errors tend to fall into one of the following six general categories:Syntax errors: You have a problem with the syntax of a formula.Forexample, a formula may have mismatched parentheses, or you may havespelled a function name incorrectly.Logical errors: A formula does not return an error, but it contains a logi-cal flaw that causes it to return an incorrect result.Incorrect reference errors: The logic of the formula is correct, but the for-mula uses an incorrect cell reference.As a simple example, the range ref-erence in a SUM formula may not include all of the data that you wantto sum.Circular references: A circular reference occurs when a formula refers toits own cell, either directly or indirectly.Circular references are useful in afew cases, but most of the time a circular reference indicates a problem.Array formula entry error: When entering (or editing) an array formula,you must use Ctrl+Shft+Enter to enter the formula.If you fail to do so,Excel does not recognize the formula as an array formula.Incomplete calculation errors: The formulas simply aren t calculatedfully.Microsoft has acknowledged some problems with Excel s calculationengine in some versions of Excel.To ensure that your formulas are fullycalculated, use Ctrl+Alt+F9.4800-x Ch21.F 8/27/01 11:59 AM Page 561Chapter 21: Tools and Methods for Debugging Formulas 561Syntax errors are usually the easiest to identify and correct.In most cases, youwill know when your formula contains a syntax error.For example, Excel won tpermit you to enter a formula with mismatched parentheses.Other syntax errorsalso usually result in an error display in the cell.The remainder of this section describes some common formula problems andoffers advice on identifying and correcting them.Mismatched ParenthesesIn a formula, every left parenthesis must have a corresponding right parenthesis.Ifyour formula has mismatched parentheses, Excel usually won t permit you to enterit.An exception to this rule involves a simple formula that uses a function.Forexample, if you enter the following formula (which is missing a closing parenthe-sis), Excel accepts the formula and provides the missing parenthesis.=SUM(A1:A500A formula may have an equal number of left and right parentheses, but theparentheses may not match properly.For example, consider the following formula,which converts a text string such that the first character is uppercase and theremaining characters are lowercase.This formula has five pairs of parentheses, andthey match properly.=UPPER(LEFT(A1))&RIGHT(LOWER(A1),LEN(A1)-1)The following formula also has five pairs of parentheses, but they are mis-matched.The result displays a syntactically correct formula that simply returns thewrong result.=UPPER(LEFT(A1)&RIGHT(LOWER(A1),LEN(A1)-1))Often, parentheses that are in the wrong location will result in a syntax error which is usually a message that tells you that you entered too many or too fewarguments for a function.Excel can help you out with mismatched parentheses.When you edit a for-mula, move the cursor to a parenthesis and pause.Excel displays it (and itsmatching parenthesis) in bold for about one second.4800-x Ch21 [ Pobierz całość w formacie PDF ]