29

Mar

2021

Calculations in Excel are performed through formulas. For example, to add up numbers A and B, we use the formula A + B. The rules governing formulas in Excel will be discussed in this blog.

The “syntax” for Excel is the set of rules that govern the structures of the formulas. Without correct syntax, the calculation cannot be performed (i.e., it is not executable).

Here is a list of syntax fundamentals for creating formulas in Excel:

- The formula should generally be entered into a specific cell (e.g., A1).
- All formulas start with an equal sign
**=**. For example, to use a formula to calculate 10 + 20 in cell A1, we must enter the formula as**= 10 + 20**. - The calculation result is stored and displayed in the cell where we enter the formula. For example, if we enter the formula
**= 10 + 20**in cell A1, cell A1 stores and displays the result 30. - The content in a cell is regarded as one formula. For example, if we enter
**= 10*2 + 20/4**in cell A1, the whole content in cell A1 is treated as one formula. - Cell references can be used in formulas. (For more details about cell references, see Cell References.)

In addition to correct syntax, an Excel formula must follow certain logical rules. Without correct logic, the calculation can be performed but the formula may not return the desired result (i.e., it is executable but may be incorrect).

Below is the order of mathematical operations:

Order of Operation | Operator |
---|---|

1^{st} |
Excel Functions |

2^{nd} |
( ) |

3^{rd} |
^ |

4^{th} |
* / |

5^{th} |
+ – |

**1. Multiplication and division must be performed before addition and subtraction.**

Example 1:

For the formula = 1 + 2 * 3, multiplication must be done first. The calculation result is 7. You would multiply 2 × 3 to get 6 and then add the 1 (1 + 6).

Example 2:

For the formula = 20 / 4 + 10 * 2, both multiplication and division must be done first. The calculation result is 25. You would divide 20 by 4 to get 5 and then multiple 10 × 2 to get 20 (5 + 20).

**2. Exponents (powers) are calculated before multiplication and division.**

Example:

For the formula = 1 + 2 * 3 ^ 4, the exponent is calculated first. The result of 81 (3 to the power of 4) is multiplied by 2 to get 162. The final result is 163 (1 + 162).

**3. Calculations inside parentheses are done before all mathematical operators.**

Example 1:

For the formula = (1 + 2) ^ 3 + 4, the calculation inside the parenthesis is done first to get the result of 3. The exponent is then computed to yield the result 27. The final result is 31 (27 + 4).

Example 2:

For the formula = (((1 + 2) * 3 – 4) ^ 2 + 5) * 6, the calculation in the innermost parenthesis is done first to yield the result of 3 (1 + 2). The calculation in the middle set of parenthesis is then performed to yield the result of 5 (3 × 3 – 4), keeping in mind the multiplication is done first. The calculation in the outermost parenthesis is done next, yielding the result of 30 (5^2 + 5). The final result is 180 (30 × 6).

**4. Excel functions are calculated before parenthesis.**

Example:

For the formula = (sum(1,2,3,4) + 5)^2, the sumfunction is executed first to get the result of 10 (1+2+3+4). The calculation in the parenthesis is then computed to yield the result of 15 (10 + 5). The final result is 225 (15 ^ 2). (For more details about Excel functions, see Excel Functions.)

Datetime data are treated as numerical values in Excel, with any difference in day altered by the number one. The syntax and logic of formulas used to calculate numerical values also applies to datetime data.

For example, the formula = “2020/1/1” + 1 adds one day to the date January 1, 2020. The result returned is January 2, 2020.

Text data, on the other hand, are treated as a string of individual characters. For example, the text “Excel” is a string of the character “E”, followed by the characters “x”, “c”, “e”, and “l”. Even if the text entry is in the form of a number, such as “20001”, the entry is treated as the character “2”, followed by three characters “0”, and one character “1” rather than the numerical value 20001. Therefore, the basic numerical operations such as addition, subtraction, multiplication, and division do not apply to text data.

At Gleim, we know learning data analytics is vital for future CPAs, CMAs, and CIAs. That is why we are offering this series of data analytics blogs and are continually updating all of our CPA, CMA, and CIA Review materials with the necessary information you need to pass your exams.

If you’re ready to get video tutorials specifically for data analytics in the CPA Exam and have more data to practice with syntaxes and logics of Excel formulas, look no further than the Gleim Premium CPA Review.

**Excel Lessons**

Excel Basics

Excel Shortcuts

Excel Calculation Rules

Cell References

Excel Functions

Function Basics

Working with Numbers

Working with Datetime Data

Working with Text Data

Logical Testing

Summarizing Data

Filtering and Sorting

Lookup Functions

Tables

Pivot Tables

**Python Lessons**

Python Basics

Conditional Statements

Loops

Functions and Modules

Numerical Python (NumPy)

Pandas Basics

Pandas Data Capture and Cleansing

Merging and Grouping

Manipulating Text and Datetime Data

Visualization

Web Scraping

Errors and Exceptions