4.7: Creating Formulas to Perform Calculations

Last updated
Save as PDF

Page ID: 14312

\( \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}}\)

Perhaps the biggest benefit of using Excel is its ability to create formulas that, when written correctly, can dynamically update when predecessor data is updated. Formulas are mathematical calculations using data in the existing workbook to calculate new values. All formulas in Excel must begin with an equal sign (=), and can contain cell references, ranges of cell references, arithmetic operators, and constants as part of the formula’s syntax. Calculations in Excel follow normal math rules as it pertains to the order of operations rule. There are mnemonics available in various dialects to help decipher the order. The following graphics illustrate the order of operations rules and different formula syntax examples:

Formulas typically reference values stored in other cells. For example, in the Accounting equation: Assets = Liabilities + Owner’s Equity, one could calculate the Owner’s Equity using a formula. Assume that the value for Total Assets resides in cell D30, and the value for Total Liabilities resides in D60. The formula for Owner’s Equity in D65 using cell references would be =D30-D60. Using cell references is advantageous because if any of the cells that generate either the Total Assets or Total Liabilities is changed, the formulas in D30, D60 and D65 will also get updated. Another advantage is when a cell containing a formula is copied to another location, the formula will dynamically update to the new location’s reference information. This is called Relative Cell Referencing. If the formulas in column D are copied to column E, the new formula for Owner’s Equity in column E will be =E30-E60. Additionally, if rows are added or deleted in the range that the Total formulas reference, the formula will automatically update to the new formula range.

Entering the syntax for a formula can be accomplished through typing the formula components, using a pointing technique or a combination of both. The pointing method can help avoid typing errors. In the Owner’s Equity example, the user would start by typing the equal sign, and then instead of typing D30, just click on D30. Continue by pressing the minus sign, and then click on cell D60, and the click the Enter check mark on the formula bar. Using the point method is even more practical when defining large cell ranges in formulas. Just make sure not to reference the active cell in the formula. This will likely create a circular reference error, which is a formula in a cell that directly or indirectly refers to its own cell.