loader image
Skip to main content
If you continue browsing this website, you agree to our policies:
Continue
x
Home

Creating Formulas

Completion requirements

Read this overview of creating formulas in Excel. Pay close attention to the figures showing how to enter formulas in a budget spreadsheet.

The second section explains relative referencing. Excel will adjust the cell reference used in a formula when you copy and paste the formula into a new location. For example, if you are summing numbers in column A, you may have a cell with the formula =SUM(A1:A10). Copying the formula cell into the next column (column B) will automatically change to =Sum(B1:B10). This feature is useful when performing the same type of calculation on multiple data sets.

The third section discusses the order of operations. When performing complex calculations, your formula must follow the correct sequence. For example, to calculate (2+8)/5, calculate the parentheses (2+8) first and divide its result by 5. Spreadsheet programs have formatting requirements for the order of operations. Pay special attention to Table 2.3, which shows the order of operations in Excel.

The final section discusses auditing formulas, which allows you to check or review the formulas you use in a spreadsheet using a simple command. You can also see which cells are referenced in each spreadsheet formula.

Creating Complex Formulas (Controlling the Order of Operations)

The next formula to be added to the Personal Budget workbook is the percent change over last year. This formula determines the difference between the values in the LY (Last Year) Spend column and shows the difference in terms of a percentage. This requires that the order of mathematical operations be controlled to get an accurate result. Table 3 shows the standard order of operations for a typical formula. To change the order of operations shown in the table, we use parentheses to process certain mathematical calculations first. This formula is added to the worksheet as follows:

  1. Click cell F3 in the Budget Detail worksheet
  2. Type an equal sign: = 
  3. Type an open parenthesis: (
  4. Click cell D3. This will add a cell reference to cell D3 to the formula. When building formulas, you can click cell locations instead of typing them
  5. Type a minus sign: −
  6. Click cell E3 to add this cell reference to the formula
  7. Type a closing parenthesis: )
  8. Type the slash symbol for division: /
  9. Click cell E3. This completes the formula that will calculate the percent change of last year’s actual spent dollars vs. this year’s budgeted spend dollars (see Figure 6)
  10. Press the ENTER key
  11. Click cell F3 to activate it
  12. Place the mouse pointer over the Auto Fill Handle
  13. When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell F11. This pastes the formula into the range F4:F11

Table 3 Standard Order of Mathematical Operations
Symbol Order
^ First, Excel executes any exponential computations first.
* or / Second, Excel performs any multiplication or division computations second. When there are multiple instances of these computations in a formula, they are executed in order from left to right.
+ or − Third, Excel performs any addition or subtraction computations. When there are multiple instances of these computations in a formula, they are executed in order from left to right.
( ) Override Standard Order: Any mathematical computations placed in parentheses are performed first and override the standard order of operations. If there are layers of parentheses used in a formula, Excel computes the innermost parentheses first and the outermost parentheses last.

Figure 6 shows the formula added to the Budget Detail worksheet to calculate the percent change in spending. The parentheses were added to this formula to control the order of operations. Any mathematical computations placed in parentheses are executed first before the standard order of mathematical operations (see Table 3). In this case, if parentheses were not used, Excel would produce an erroneous result for this worksheet.

Adding the Percent Change Formula - Excel performs computations in parentheses first =(D3-E3)/E3

Figure 6 Adding the Percent Change Formula


Figure 7 shows the result of the percent change formula if the parentheses are removed. The formula produces a result of a 299900% increase. Since there is no change between the LY spend and the budget Annual Spend, the result should be 0%. However, without the parentheses, Excel is following the standard order of operations. This means the value in cell E3 will be divided by E3 first (3,000/3,000), which is 1. Then, the value of 1 will be subtracted from the value in cell D3 (3,000−1), which is 2,999. Since cell F3 is formatted as a percentage, Excel expresses the output as an increase of 299900%.

Removing the Parentheses from the Percent Change Formula - without parentheses, Excel calculates E3/E3 first in =D3-E3/E3

Figure 7 Removing the Parentheses from the Percent Change Formula

Integrity Check

Does the Output of Your Formula Make Sense?

It is important to note that the accuracy of the output produced by a formula depends on how it is constructed. Therefore, always check the result of your formula to see whether it makes sense with data in your worksheet.

As shown in Figure 7, a poorly constructed formula can give you an inaccurate result. In other words, you can see that there is no change between the Annual Spend and LY Spend for Household Utilities. Therefore, the result of the formula should be 0%. However, since the parentheses were removed in this case, the formula is clearly producing an erroneous result.

Prev Section
Next Activity