 |
|
|
There is another method that we can use to troubleshoot and this is with the Auditing feature of Excel. This feature is mainly used to try and pin point any errors that your formulas are producing. To be honest with you I have only ever used this out of curiosity and do not find it all that helpful. I put this down to the fact that when I see an error in a formula cell I have a pretty good idea what the problem is by the error type being generated. We will run through it though as you may need it at some stage.
Go to Tools>Forumla Auditing to see the Formula Auditing sub-menu, or right click in the toolbar area of your screen and select Auditing to bring up the Auditing Toolbar. We will look at the options on the Toolbar here.
Checks your worksheet for errors and if it finds them, brings up a dialog box showing you the type of error you have got, and giving options to fix it.
Draws tracer arrows from the cells that supply values directly to the formula in the active cell (precedents). To trace the cells that supply values indirectly to the formula in the active cell, click the Trace Precedents button again.
Removes tracer arrows from one level of dependents on the active worksheet.
Draws a tracer arrow to the active cell from formulas that depend on the value in the active cell. To add additional levels of indirect dependents, click the Trace Dependents button again.
Removes tracer arrows from one level of precedents on the active worksheet. To remove the next level of arrows, click the Remove Dependent Arrows button again.
Removes all tracer arrows from the worksheet.
If the active cell contains an error value such as #VALUE or #DIV/0, draws tracer arrows to the active cell from the cells that cause the error value.
Inserts a comment at the insertion point.
Identifies all cells that contain values that are outside the limits you set by using the Validation command on the Data menu. To see what data restrictions and messages are in effect for a cell, click the circled cell, and then click Validation on the Data menu.
Hides circles around cells that contain values outside the limits you set by using the Validation command on the Data menu. To see what data restrictions and messages are in effect for a cell before you remove circles, click the circled cell, and then click Validation on the Data menu.
Please note that if your are not familiar with Validation let me know and we will cover it in the next lesson. It is a very useful function.
Will allow you to add a formula that frequently generates an error to a window to keep an eye on it for any future error messages.
Allows you to breakdown and edit a formula containing an error, effective with nested formulas. This option is an alternative to the Insert Function Dialog, which can be used equally as efficiently to break down formulas.
Let's create an error in a formula to see how the Auditing Toolbar can help us. We will use the formula =IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no").
· First
of all, Cut and Paste the formula into cell H30.
· Now
in cell A1 enter this formula =na(). This will create the
#N/A error for us. If
you now look at cell H30 it too will be reading #N/A.
Let's see how the Auditing toolbar can help us!
· Click
in cell H30, the click the Trace Precedents button on the Auditing
toolbar. You should now see two arrows leading into cell H30, one
red and one blue. These are coming from cells A1 and A2
· To
move directly to these cells, double click on either the red or blue
arrow head. To move back to the formula cell, double click the round dot at the
start of the arrow.
· Once
back in the formula cell, click Remove Precedent Arrows.
· Click
in cell A1 and click the Trace Dependents button and you will
have a red coloured arrow
leading to the formula cell. Again you can navigate straight to the cell by
double clicking on the dot.
· Make
sure you are back in cell A1 and click Remove Dependent Arrows.
· Now
with H30 selected click on the Trace Error button. Excel will
draw a red arrow and immediately take you to the error
cell.
· Click
Remove All Arrows
In case you don't realise, when using the Auditing toolbar a red arrow means an error. That is why when we initially clicked in cell H30 and then clicked the Trace Precedents button Excel used a red arrow coming from cell A1 (red means error) and a blue one from cell A2.
Ok, armed with this information you will eventually be able to confidently write those very long complicated Excel formulas you have no doubt seen. If somebody asks you how it works (or why it doesn't work) you won't need to make out you are suddenly very busy and run away! By the time you have worked your way through it they will either think your are a related to Albert Einstein or meet you with a blank stare, either way they will know that, you at least know what your talking about (and they will be right).