Formulas, Relative References, Order of Operations, and Auditing Formulas
Read this overview of how to create formulas in Excel. Pay close attention to the figures that show 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). If you copy the formula cell into the next column (column B), it will automatically change to =Sum(B1:B10). This feature is useful when you are performing the same type of calculation on multiple sets of data.
The third section discusses the order of operations. When you perform complex calculations, your formula must follow the correct order of operations. For example, to calculate (2+8)/5, you first calculate the parentheses (2+8) and then 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 Excel follows.
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 a Basic Formula
Formulas are used to calculate a variety of mathematical outputs in Excel and can be used to create virtually any custom calculation required for your objective. Furthermore, when constructing a formula in Excel, you use cell locations
that, when added to a formula, become cell references . This means that Excel uses, or references, the number entered into the cell location when calculating a mathematical output. As a result, when the numbers in the cell references
are changed, Excel automatically produces a new output. This is what gives Excel the ability to create a variety of what-if scenarios, which will be explained later in the chapter.
To demonstrate the construction of a basic formula, we will begin working on the Budget Detail worksheet in the Personal Budget workbook, which is shown in Figure 2 Budget Detail Worksheet. To complete this worksheet,
we will add several formulas and functions. Table 1 Spend Category Definitions provides definitions for each of the spend categories listed in the range A3:A11. When you develop a personal budget, these categories are defined on the
basis of how you spend your money. It is likely that every person could have different categories or define the same categories differently. Therefore, it is important to review the definitions in Table 1 Spend Category Definitions to understand how we are defining these categories before proceeding.
Figure 2 Budget Detail Worksheet
|Household Utilities||Money spent on electricity, heat, and water and on cable, phone, and Internet access|
|Food||Money spent on groceries, toiletries, and related items|
|Gasoline||Money spent on fuel for automobiles|
|Clothes||Money spent on clothes, shoes, and accessories|
|Insurance||Money spent on homeowner’s or automobile insurance|
|Taxes||Money spent on school and property taxes (this example of the personal budget assumes that we own property).|
|Entertainment||Money spent on entertainment, including dining out, movie and theater tickets, parties, and so on|
|Vacation||Money spent on vacations|
|Miscellaneous||Includes any other spending categories, such as textbooks, software, journals, school or work supplies, and so on|
Table 1 Spend Category Definitions
The first formula that we will add to the Budget Detail worksheet will calculate the Monthly Spend values. The formula will be constructed so that it takes the values in the Annual Spend column and divides them by 12. This will show how much money will be spent per month for each of the categories listed in Column A. The following explains how this formula is created:
- Click the Budget Detail worksheet tab to open the worksheet.
- Click cell C3.
- Type an equal sign (=). When the first character entered into a cell location is an equal sign, it signals Excel to perform a calculation or produce a logical output.
- Type D3. This adds D3 to the formula, which is now a cell reference. Excel will use whatever value is entered into cell D3 to produce an output.
- Type the slash symbol (/). This is the symbol for division in Excel. As shown in Table 2 Excel Mathematical Operators, the mathematical operators in Excel are slightly different from those found on a typical calculator.
- Type the number 12. This divides the value in cell D3 by 12. In this formula, a number, or constant, is used instead of a cell reference because it will not change. In other words, there will always be 12 months in a year.
- Press the ENTER key.
Table 2 Excel Mathematical Operators
Use Cell References
Cell references enable Excel to dynamically produce new outputs when one or more inputs in the referenced cells are changed. Cell references also allow you to trace how outputs are being calculated in a formula. As a result, you should never use a calculator to determine a mathematical output and type it into the cell location of a worksheet. Doing so eliminates Excel’s cell-referencing benefits as well as your ability to trace a formula to determine how outputs are being produced.
Figure 3 Adding a Formula to a Worksheet shows how the formula appears in cell C3 before you press the ENTER key. Figure 4 Formula Output for Monthly Spend shows the output of the formula after you press the ENTER key. The monthly spend for Household Utilities is $250 because the formula is taking the Annual Spend in cell D3 and dividing it by 12. If the value in cell D3 is changed, the formula automatically produces a new output. We are calculating the spend per month for each category because people often get paid and are billed for these items on a monthly basis. This formula allows you to compare your monthly income to your monthly bills to determine whether you have enough income to pay these expenses.
Figure 3 Adding a Formula to a Worksheet
Figure 4 Formula Output for Monthly Spend
Use Universal Constants
If you are using constants, or numerical values, in an Excel formula, they should be universal constants that do not change, such as the number of days in a week, weeks in a year, and so on. Do not type the values that exist in cell locations into an Excel formula. This will eliminate Excel’s cell-referencing benefits, which means if the value in the cell location you are using in a formula is changed, Excel will not be able to produce a new output.