In Excel 2003, you'll find Evaluate Formula on the Tools menu under Auditing. It's a bit more methodical but certainly useful. The Evaluate Formula option on the Formulas tab (in the Formula Auditing group) works similarly. Shouldn't this expression return "is down?" Figure BĮvaluating a portion of the formula in the Formula bar helps you pinpoint logic errors. It's not just for troubleshooting formulas. You can also use the Formula bar to learn a constant's value and to discover errors caused by invalid data. This works, this time, because the expression follows the conditional logic: $44,075 is less than $45,962. When evaluating the expression in the Formula bar, as shown in Figure B, you can quickly see that the true and false actions don't match the conditional statement's intent.
Figure A shows a simple IF() function that returns the string "is down" if the yearly total for 2011 is less than 2010 - well, it's supposed to. If that doesn't work, check the Advanced Editing Options.) Press when you're done. (To edit in-cell, simply double-click the cell. Either way, you can quickly pinpoint specific problems with logic. Specifically, you highlight a cell reference or simple equation, press, and Excel evaluates the highlighted expression and returns the result in the Formula bar. One of the quickest ways to track down a problem is to evaluate individual components in the Formula bar or even right in the cell. For instance, you might display the message, "value not found" - the user understands that - whereas the #N/A value might cause confusion. Specifically, in this case, you'd wrap the original function in an error-handling routine that displays a meaningful message or more appropriate value. Fixing this error requires error handling. When one of these functions returns #N/A, it usually means it can't find the value you're looking for or that you haven't sorted the list (which isn't always necessary). This error shows itself a lot with VLOOKUP() and HLOOKUP(). It means that Excel can't return a result that makes sense. The most common cause for this error value is a reference to text by a numeric operation.Įxcel's #N/A error value isn't like the rest - there's usually nothing wrong with the formula itself.
For instance, the expression =A1+B1 won't return an error if you delete the value in B1.
These 10 tips will help even the most experienced Excel users find common errors quickly. Sometimes, just a bit of special knowledge can help you resolve an error. Excel offers some valuable auditing tools, found on the Formulas tab in the Formula Auditing group, but you won't always need that much power. Troubleshooting an Excel worksheet's formulas and functions can be a big job.
0 kommentar(er)
