From time to time I get asked the question “what order do formulas evaluate?”
The acronym BEDMAS can help you remember. It stands for:
Brackets: Any operation(s) contained in brackets will be carried out first followed by any exponents.
Exponents: Then any exponents like ^ or SQRT
Division or Multiplication (left to right): Excel considers these to be of equal importance, and carries out these operations in the order they occur from left to right in the equation.
Addition or Subtraction: The same goes for addition and subtraction. They are considered equal in the order of operations. Whichever one appears first in an equation, either addition or subtraction, is the operation carried out first.
Ok, so what if you know all that (after all you probably learnt BEDMAS at school way-back-when) but you’re still stuck because your formula isn’t returning the result you want.
Well, thankfully Excel has a tool for that too.
Evaluate Formula Tool
The Evaluate Formula tool allows us to see how each component of a formula evaluates, one step at a time.
Let’s take the VLOOKUP formula we looked at last week as an example:
How to use the Evaluate Formula Tool
- Select the cell containing the formula you want to evaluate. Ours is in G6.
- On the Formulas tab of the Ribbon in the Formula Auditing group select Evaluate Formula.
The Evaluate Formula dialog box will open:
- Click the ‘Evaluate’ button to view the value of the underlined reference. In the example above the underlined reference is cell D6, and you can see below it evaluates to ‘William’.
- If the underlined reference is part of another formula you can use the ‘Step In’ button to display the other formula. Then ‘Step Out’ to go back and continue evaluating.
- In the image below you can see all but the last step of the formula evaluated:
- And finally the result:
The Evaluate Formula tool is especially useful for nested formulas that may not be returning the correct result.
I also like to use it to find why I’m getting # errors or checking that the result is calculating as I expect.
But be warned; it can’t work miracles. If you can’t get Excel to even accept your formula then you may need to consult your office Guru first 🙂
Robert Prather says
Something to remember from our Highschool days.
Here’s a question for you, is there anyway to tell from the error that excel returns (#Name, #Value) as to what might be wrong?
Mynda Treacy says
Hi Bobcat,
Yes, you can tell from the error what might be wrong. I haven’t written a list of them but you can find one at the link below (see the right hand side of the page under ‘See also’):
http://office.microsoft.com/en-us/excel-help/correct-common-errors-in-formulas-HA010066323.aspx
I should add writing about that to my To-Do list 🙂
Kind regards,
Mynda.