Chapter 7 continued ...
| |
Tip – Double-click a cell that contains links to mark all precedent cells in the current worksheetFrom Tools, select Options, Edit. Clear the Edit directly in cell option, and click OK. |
| New
In |
|
| Step into the formula’s calculations, one step at a time. From the Tools menu, select Audit formulas, Evaluate formula, or from the Audit formulas toolbar, click Evaluate formula. The Evaluate formula dialog box allows you to move between the calculations in a formula. Click Step In to view a calculation that is part of this formula. |
|
Entering formulas into cells in Excel sheets is not foolproof. It is impossible to completely avoid mistakes, but there are ways to keep them to a minimum.
The method described in Nesting Formulas (see above) will help you to create formulas inside other formulas. Combining the IF formula with the ISERROR formula returns a calculation whose result is TRUE, if the result of a calculation returns an error.
1. In Cell C1, enter the formula =ISERROR(B1). The formula returns the result TRUE. That is, Cell B1 contains an error in the calculation of the formula (the formula ISERROR is located in the category Information in the Paste Function dialog box).
2. In Cell D1, enter an IF formula =IF(C1,0,B1).
3. Combine the formulas into one nested formula. From Cell C1, copy the formula (without the = sign), and paste it into Cell D1 instead of C1.
4. From Cell B1, copy the formula (without the = sign), and paste it twice – instead of the digit 0 and instead of B1.
The result – one nested formula: =IF(ISERROR(A1/A2),0,A1/A2).
Marking Cells Containing Errors
Mark cells containing errors so that they can appear in color, be easily identified,
or be deleted.
Technique 1
1. Press F5, or from the Edit menu, select Go To…
2. In the Go To dialog box, click Special…
3. Select Formulas, Errors.
4. Click OK.
Technique 2
Use conditional formatting to mark and/or color cells containing errors.
1. Make A1 be the active cell. Press Ctrl+A to select the sheet.
2. From the Format menu, select Conditional Formatting.
3. In Condition 1, select Formula Is.
4. In the formula box, type the formula =IsError(A1). Now click Format, and select the desired properties.
5. Click OK.
| New
In |
|
| In Cell A1, type the number 100. In Cell B1, type the formula =A1/A2. The result of the calculation returns the error #!DIV/0!. Select Cell B1 with the error, and click the Error checking icon (the first icon on the left on the Auditing formulas toolbar. |
|
| New
In |
|
| The smart tag for handling errors is created automatically in the cell whose calculation returned an error. Open the shortcut menu by clicking the small Smart tag arrow. Through the shortcut menu, you can get an explanation of the type of error in the formula, edit the formula, ignore errors, and view the evaluation steps by selecting Show Calculation Steps… |
|
