Hello Excellers. I am excited to share with you some more Formula Fun, and in particular a checklist to double check your Excel formulas are on point!.
Let’s face it, formulas are awesome, but they can can also cause huge headaches even with a tiny mistake, Excel will go ahead and do what you tell it to, so you have to make sure your are giving it the right instructions. Here is a simple checklist to double check your Excel formula work.
1. Are You Mixed Up?…..
It is surprisingly easy to get all mixed up especially if you have a tonne of formulas going on in your worksheet. Sometimes it is not so easy to spot mistakes with mix ups.
This formula =D2-D3 looks at lot like =D3-D2. Take a few minutes to ensure there are no small discrepancies like this one.
2. Roughly Know What You Answer Should Be…..
If you are able to, then take a stab at a ballpark result for your formula. I do this when checking my formulas, for example the calculation maybe the percentage difference between two sales figures as in my example below. I know that it should be a negative number, and around about a quarter?. This gives ball park assessment to help me spot and glaring issues with my formula. This is a very basic way to check formulas, like in my example knowing it is a negative number, but anything more specific, it’s not so useful. You can always substitute some simple numbers in the formula to make it easier to check what the answer should be.
3. Check The Order Of Precedents or Operations.
What the heck does that mean?, well, it is back to some basic mathematics class…. but this relates to the order in which Excel calculates.
It’s important to understand that when you create a formula with several operators, Excel evaluates and performs the calculation in a specific order. For instance, Excel always performs multiplication before addition. This order is called the order of operator precedence. You can force Excel to override the built-in operator precedence by using parentheses to specify which operation to evaluate first.
Then order of calculations can be easily remembered by the acronym BEDMAS
Brackets
Exponents
Division
Multiplication
Addition
Subtraction
Here is an example. The following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
=(5+2)*3
See how this changes things?…. If you want to see more example of this in action check out my Blog Post Formula Friday – How Excel Calculates – The Order Of Excel Calculations.
4. Switch Your View To Show Formulas…
A really quick way to check formulas is to use the Show Formula feature. This allows you to see all of the formulas on your worksheet at the same time. There are a few ways to do this.
- Hit the CTRL+` shortcut (the grave accent)
- Formulas Tab – Formula Auditing – Show Formulas
You can now see the formulas.
5. Ensure All Formula Arguments Are Accounted For…
If you are using a Function in Excel then it may have multiple arguments which are required. If this is the case then make sure they are all accounted for. As you begin to type the function, Excel will display the dialog box to guide you through the parts of the formula.
Formulas get easier with practice and even more practice. If your formula returns an error, or is not giving an expected result, then work through some troubleshooting in the steps above. The error is usually obvious once you look for it and then easily fixed.
Don’t forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
