We’ve all been there… a formula either isn’t accepted by Excel (syntax error), or it gives incorrect results (logic errors). Let’s look at these two cases in turn and see how you can best avoid or fix them.
- Syntax errors
You finish typing in a formula, press enter, and Excel won’t accept it.
You get a hint such as “You’ve entered too few arguments for this function”. This may sound like Excel being fussy, but it really is quite helpful because you know what the error is, and this helps you find and fix it.
Here is a simple example using the function ROUND.
This simply means you were too quick to press the enter key. 😊
Watch the friendly tool tip which appears under the formula bar when you enter a valid function name followed by an open bracket.
Here Excel shows you exactly which argument you are currently entering by marking the name of the argument in bold. In this case, we have entered a cell reference referring to the cell containing the value that we wish to round and can see that we need a second argument called num_digits. If you are not sure what this means then you have two options.
- FX: Click on the “fx” icon in the formula bar and you open up the helpful “Function Arguments” dialog box which looks like this.
Here you can see the Num_digits argument and on the right-hand side it tells you it needs to be a number. Underneath you get a more detailed explanation. If this still is not enough then click on the link ”Help on this function” at the bottom to open up more details in a browser. These Microsoft help files contain explanations, helpful examples and often a video to help you understand.
- F1: Alternatively simply press F1 to call up the (shorter) help file within Excel.
If you know the function name and the arguments required you can type them in without paying attention to the helpful tooltip. But if you mistype a function name, you will get a #NAME error such as here where I have typed rund instead of round.
Simply retype your function name more slowly and Excel shows you a list of those functions containing the letters you have already typed for you to select the correct one with the correct spelling. It also gives you a helpful short explanation of what the first function does in a tooltip box.
To see what any of the other functions in the list do, simply move down the list with the down arrow on your keyboard or select one with your mouse.
Finally a word on optional arguments. If Excel shows you an argument in square brackets this means it is optional i.e. you don't need to enter anything and can simply close the brackets after you have entered the last compulsory argument (one not in square brackets). Here is an example with the MATCH function where the final argument match_type is optional.
This simply means it is technically optional, but an entry may be very necessary to ensure your function works as desired. In fact with the functions MATCH and VLOOKUP it is usually essential for correct functioning to enter a match type FALSE or 0 to ensure an exact match, otherwise Excel will simply look for the closest matching item, which may give you an incorrect result.
This brings us nicely to the second type of error which is harder to fix.
- Logic errors
Here you have identified that although Excel has accepted your formula, it gives you an incorrect result.
The first step to solving this type of error is to use the F2 key to analyse the components of your formula.
In this example we can quickly identify that the formula refers to an incorrect cell.
In more complicated examples e.g. with nested IFs or AND conditions, it can be very helpful to click on a selected argument in the tooltip and Excel then marks the relevant section of your formula.
This can be helpful for identifying missing or misplaced brackets or separators (commas or, in this screenshot, semi-colons).
If that still doesn’t solve an issue, you can enlist the help of the Evaluate Formula tool on the Formulas ribbon.
This opens the evaluate formula dialogue box which shows you your formula and by clicking on the evaluate button at the bottom (or by pressing the enter key) Excel shows you how it evaluates the formula step by step so you can see exactly where you may be having a problem and correct the formula.
I hope these tips help to you to avoiding formula errors or, failing that, to detect and correct them.
For lots more tips on how to avoid detect and correct errors please refer to my book “Avoid Excel horror stories” available on Amazon.
