About Excel Formula Syntax: The Language of Formulas and Functions

Excel makes it a simple task to perform mathematical operations. Using Excel formula syntax, you can calculate and analyze data in your worksheet. As a reminder:

• Formulas are equations that combine values and cell references with operators to calculate a result.
• Functions are prebuilt formulas that can be quickly fed values without the need to write the underlying formula yourself.

But to use either, you need to know how to write in their own language, which is commonly referred to as operators. And, like any language, operators have their own form of grammar, referred to as Order of Precedence.

 Operators Used in Formulas and Functions

Mathematical Operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; to combine numbers; and to produce numeric results, use the following arithmetic operators.

Examples:

Comparison Operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.

Example:

Text Concatenation Operator

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.

Example:

Order of Precedence in which Excel Performs Operations

The order in which a calculation is performed affects the result, so it is important to understand how the order is determined and how you can change it to obtain desired results.

A formula in Excel always begins with an equal sign (=). The equal sign tells Excel that the succeeding characters are part of a formula or function. After the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Excel calculates from left to right, using the PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) order of operations.

In other words, it performs calculations in parentheses first, then it checks for multiplication and division, then finally it performs addition and subtraction. Using these rules of math is what makes it possible to do some potentially confusing problems that have many possible results if you do not follow the right order. Knowing that this is how Excel reads math, you need to structure your formulas accordingly.

2+3×4-5/6=?

If this problem were performed just from left to right, the answer would be 2.5. However, your intent might have been very different. Adding parentheses to show which items should be calculated first helps.

(2+(3×4)-5)/6

This same set of numbers with parentheses added calculate to a much different total. 3×4 is calculated first, for a total of 12. Then 2 is added to get 14, from which 5 is subtracted to get 9. Finally, 9 is divided by 6 for a total of 1.5.

Now that you have the foundation of Excel formula syntax – the language and grammar – play around with syntax or check out a few other posts to practice formula building!