Chapter 11_M2

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 11: MAKING YOUR FORMULAS ERROR- FREE Structure 1.0 Learning Objectives 1.1 Introduction 1.2 Identifying and correcting common formula errors 1.3 Using Excel auditing tools 1.4 Using formula AutoCorrect 1.5 Tracing cell relationships 1.6 Checking spelling and related features 1.7 Summary 1.7 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain Identifying and correcting common formula errors in MS Excel?  Explain auditing tools in MS Excel  Explain formula autocorrect in MS Excel  Explain tracing cell relationships in MS Excel  Explain checking spelling and related features 1.1 Introduction It goes without saying that you want your Excel worksheets to produce accurate results. Unfortunately, it's not always easy to be certain that the results are correct, especially if you deal with large, complex worksheets. This chapter introduces the tools and techniques available to help identify, correct, and prevent errors. Page 55 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 Finding and Correcting formula Errors Making a change in a worksheet — even a relatively minor change — may produce a ripple effect that introduces errors in other cells. For example, accidentally entering a value into a cell that previously held a formula is all too easy to do. This simple error can have a major impact on other formulas, and you may not discover the problem until long after you make the change — if you discover the problem at all. Formula errors tend to fall into one of the following general categories:  Syntax errors: You have a problem with the syntax of a formula. For example, a formula may have mismatched parentheses, or a function may not have the correct number of arguments.  Logical errors: A formula doesn't return an error, but it contains a logical flaw that causes it to return an incorrect result.  Incorrect reference errors: The logic of the formula is correct, but the formula uses an incorrect cell reference. As a simple example, the range reference in a Sum formula may not include all the data that you want to sum.  Semantic errors: An example is a function name that is spelled incorrectly. Excel will attempt to interpret it as a name and will display the #NAME? error.  Circular references: A circular reference occurs when a formula refers to its own cell, either directly or indirectly. Circular references are useful in a few 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 press Ctrl+Shift+Enter to enter the formula. If you fail to do so, Excel doesn't recognize the formula as an array formula, and you may get an error or incorrect results.  Incomplete calculation errors: The formulas simply aren't calculated fully. To ensure that your formulas are fully calculated, press Ctrl+Alt+Shift+F9. Page 56 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS Syntax errors are usually the easiest to identify and correct. In most cases, you'll know when your formula contains a syntax error. For example, Excel won't permit you to enter a formula with mismatched parentheses. Other syntax errors also usually result in an error display in the cell. Mismatched parentheses In a formula, every left parenthesis must have a corresponding right parenthesis. If your formula has mismatched parentheses, Excel usually won't permit you to enter it. An exception to this rule involves a simple formula that uses a function. For example, if you enter the following formula (which is missing a closing parenthesis), Excel accepts the formula and provides the missing parenthesis: =SUM(A1:A500 A formula may have an equal number of left and right parentheses, but the parentheses may not match properly. For example, consider the following formula, which converts a text string such that the first character is uppercase and the remaining characters are lowercase. This formula has five pairs of parentheses, and they match properly: =UPPER(LEFT(A1))&RIGHT(LOWER(A1),LEN(A1)-1) The following formula also has five pairs of parentheses, but they're mismatched. The result displays a syntactically correct formula that simply returns the wrong 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 few arguments for a function. Tip Excel can help you with mismatched parentheses. When you're editing a formula and you move the cursor over a parenthesis, Excel displays it (and its matching parenthesis) in bold for about one-half second. In addition, Excel color-codes pairs of nested parentheses while you're editing a formula. Page 57 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.3 Using Formula AutoCorrect When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers a suggested correction. The accompanying figure shows an example of a proposed correction. Be careful when accepting corrections for your formulas from Excel because it doesn't always guess correctly. For example, I entered the following formula (which has mismatched parentheses): =AVERAGE(SUM(A1:A12,SUM(B1:B12)) Excel then proposed the following correction to the formula: =AVERAGE(SUM(A1:A12,SUM(B1:B12))) You may be tempted to accept the suggestion without even thinking. In this case, the proposed formula is syntactically correct, but it's not what I intended. The correct formula is =AVERAGE(SUM(A1:A12),SUM(B1:B12)) Page 58 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS Some more errors are:  Cells are filled with hash marks, blank cells are not blank,extra space characters, formulas returning an error, tracing Error Values, #DIV/0! Errors, #N/A errors, #NAME? errors, #NULL! Errors, #NUM! errors, #REF! errors, #VALUE! Errors, Absolute/relative reference problems, etc. 1.4 Using Excel Auditing Tools Excel includes a number of tools that can help you track down formula errors. This section describes the auditing tools built in to Excel. Identifying cells of a particular type The Go to Special dialog box (shown in Figure 31.6) is a handy tool that enables you to locate cells of a particular type. To display this dialog box, choose Home => Editing =>Find & Select => Go to Special. You can use the Go to Special dialog box to select cells of a certain type, which can often help you identify errors. For example, if you choose the Formulas option, Excel selects all the cells that contain a formula. If you zoom the worksheet out to a small size, you can get a good idea of the worksheet's organization. Page 59 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.5 Tracing Cell Relationships To understand how to trace cell relationships, you need to familiarize yourself with the following two concepts:  Cell precedents: Applicable only to cells that contain a formula, a formula cell's precedents are all the cells that contribute to the formula's result. A direct precedent is a cell that you use directly in the formula. An indirect precedent is a cell that isn't used directly in the formula but is used by a cell that you refer to in the formula.  Cell dependents: These formula cells depend upon a particular cell. A cell's dependents consist of all formula cells that use the cell. Again, the formula cell can be a direct dependent or an indirect dependent. For example, consider this simple formula entered into cell A4: =SUM(A1:A3) Cell A4 has three precedent cells (A1, A2, and A3), which are all direct precedents. Cells A1, A2, and A3 all have at least one dependent cell (cell A4). Identifying cell precedents for a formula cell often sheds light on why the formula isn't working correctly. Conversely, knowing which formula cells depend on a particular cell is also helpful. For example, if you're about to delete a formula, you may want to check whether it has any dependents. 1.6 Checking spelling and related features Using the background error-checking feature: Some people may find it helpful to take advantage of the Excel automatic error-checking feature. This feature is enabled or disabled via the Enable Background Error Checking check box, found on the Formulas tab of the Excel Options dialog box. In addition, you can use the check boxes in the Error Checking Rules section to specify which types of errors to check. Page 60 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS When error checking is turned on, Excel continually evaluates the formulas in your worksheet. If a potential error is identified, Excel places a small triangle in the upper-left corner of the cell. When the cell is activated, a drop-down control appears. Clicking this drop-down control provides you with options. The options that appear when you click the drop-down control in a cell that contains a #DIV/0! error. The options vary, depending on the type of error. In many cases, you'll choose to ignore an error by selecting the Ignore Error option. Selecting this option eliminates the cell from subsequent error checks. However, all previously ignored errors can be reset so that Page 61 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS they appear again. (Use the Reset Ignored Errors button on the Formulas tab of the Excel Options dialog box.) You can choose Formulas image Formula Auditing image Error Checking to display a dialog box that describes each potential error cell in sequence, much like using a spell-checking command. This command is available even if you disable background error checking. This dialog box is modeless; that is, you can still access your worksheet when the Error Checking dialog box is displayed. Excel provides another way to evaluate a part of a formula: 1. Select the cell that contains the formula. 2. Press F2 to get into Cell Edit mode. 3. Use your mouse to highlight the portion of the formula you want to evaluate. Or press Shift and use the navigation keys. 4. Press F9. The highlighted portion of the formula displays the calculated result. You can evaluate other parts of the formula or press Esc to cancel and return your formula to its previous state. Page 62 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.7 Summary  Making a change in a worksheet — even a relatively minor change — may produce a ripple effect that introduces errors in other cells.  Syntax errors are usually the easiest to identify and correct.  When you enter a formula that has a syntax error, Excel attempts to determine the problem and offers a suggested correction.  Cell dependents are the formula cells depend upon a particular cell.  Cell precedents are applicable only to cells that contain a formula, a formula cell's precedents are all the cells that contribute to the formula's result. 1.8 References https://apprize.best/microsoft/excel_9/31.html https://exceljet.net/excel-formula-errors https://www.exceltip.com/excel-errors/formula-errors-in-excel-and- solutions.html https://www.datamation.com/applications/error-free-worksheets-in- excel-2007/ https://support.microsoft.com/en-us/office/detect-errors-in-formulas- 3a8acca5-1d61-4702-80e0-99a36a2822c1 Page 63 of 63 All Rights Reserved. Vol. TLE001/03-2022