Excel Formulas and the Order of Operations

Excel formulas generally carry out operations as they appear in a formula left to right, but not always. "First things first, but not necessarily in that order" (Doctor Who). Learn more about how to write formulas that correctly calculate a result.

Just as in elementary school math, Excel follows a specific hierarchy about what to do first in a series of math operations. Reference operators come first. If you include a range such as D5:D19 in your formula, Excel must recognize that block of data before it can apply functions to the data. I should also note that there are two different kind of cell or range references: relative and absolute. A relative reference changes when the formula moves to a new cell. Absolute references do not. The reference "D5:D19" is a relative reference, while "$D$5:$D$19" is absolute. You can also do "D$5:D$19", which makes only the row numbers absolute, while the column references remain relative. Confusing, huh?

Also, multiplication and division precede addition and subtraction, and comparison operations such as the greater than and less than signs are processed last. Putting parentheses around an operation, however, gives it top priority. Everything inside the parentheses will be calculate first, before anything else in the formula.

When writing formulas in Excel, it's important to understand how Excel will process its contents. Otherwise, you may find yourself working with completely incorrect results, and you won't know how to fix your formulas to get the results you want.