5.2: Understanding Complex Formulas

Last updated
Save as PDF

Page ID: 14247

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

Formulas can be simple mathematical formulas or complicated formulas involving multiple mathematical operations, multiple cell ranges and nested functions. While it is a good idea to remember the order of operations rules, complex formulas can use parentheses to identify the arguments of functions and to override the order of operations. In mathematical operation formulas, operations within parentheses are performed before those outside of it. For example, in =A3+B3*C3, B3 is multiplied by C3 before A3 is added to the result, but in =(A3+B3)*C3, A3 and B3 are added together first, then the result is multiplied by C3.

Parentheses in operations may be nested inside each other. The operation in the innermost set of parentheses will be performed first. Whether nesting parentheses in mathematical operations or in nested functions, always be sure to have as many closed parentheses in the formula as there are open parentheses, or Excel will return an error message. In the illustrated worksheet below, cell D30 contains a complex formula that calculates the differences between rooms available and rooms rented to determine the total number of unrented rooms in the Balcony and Penthouse categories. The formula in D30 is: =(SUM(C5:C8)-SUM(D5:D8))+(SUM(C25:C28)-SUM(D25:D28)). Notice the number of open and closed parenthesis!