In a complex formula, how does excel determine which calculation to perform first?
Learning Objectives
Show
This section reviews the fundamental skills for entering formulas into an Excel worksheet. The objective used for this chapter is the construction of a personal cash budget. Most financial advisors recommend that all households construct and maintain a personal budget to achieve and maintain strong financial health. Organizing and maintaining a personal budget is a skill you can practice at any point in your life. Whether you are managing your expenses during college or maintaining the finances of a family of four, a personal budget can be a vital tool when making financial decisions. Excel can make managing your money a fun and rewarding exercise. Figure 2.1 shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains four worksheets. The first worksheet, Budget Summary, contains formulas that utilize or reference the data in the other three worksheets. As a result, the Budget Summary worksheet serves as an overview of the data that was entered and calculated in the other three worksheets of the workbook. Figure 2.1 Completed Personal Budget WorkbookCreating a Basic FormulaDownload Data File:
CH2 Data 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.2. To complete this worksheet, we will add several formulas and functions. Table 2.1 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 2.1 to understand how we are defining these categories before proceeding. Figure 2.2 Budget Detail WorksheetTable 2.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:
Table 2.2 Excel Mathematical Operators
Why?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 2.3 shows how the formula appears in cell C3 before you press the ENTER key. Figure 2.4 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 2.3 Adding a Formula to a WorksheetFigure 2.4 Formula Output for Monthly SpendWhy?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. Relative References (Copying and Pasting Formulas)Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations. For example, Figure 2.4 shows the output of the formula that was entered into cell C3. However, this calculation needs to be performed for the rest of the cell locations in Column C. Since we used the D3 cell reference in the formula, Excel automatically adjusts that cell reference when the formula is copied and pasted into the rest of the cell locations in the column. This is called relative referencing and is demonstrated as follows:
Figure 2.5 shows the outputs added to the rest of the cell locations in the Monthly Spend column. For each row, the formula takes the value in the Annual Spend column and divides it by 12. You will also see that cell D6 has been double clicked to show the formula. Notice that Excel automatically changed the original cell reference of D3 to D6. This is the result of relative referencing, which means Excel automatically adjusts a cell reference relative to its original location when it is pasted into new cell locations. In this example, the formula was pasted into eight cell locations below the original cell location. As a result, Excel increased the row number of the original cell reference by a value of one for each row it was pasted into. Figure 2.5 Relative Reference ExampleWhy?Use Relative Referencing Relative referencing is a convenient feature in Excel. When you use cell references in a formula, Excel automatically adjusts the cell references when the formula is pasted into new cell locations. If this feature were not available, you would have to manually retype the formula when you want the same calculation applied to other cell locations in a column or row. 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 2.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:
Table 2.3 Standard Order of Mathematical Operations
Figure 2.6 shows the formula that was 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 2.3). In this case, if parentheses were not used, Excel would produce an erroneous result for this worksheet. Figure 2.6 Adding the Percent Change FormulaFigure 2.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%. Figure 2.7 Removing the Parentheses from the Percent Change FormulaIntegrity CheckDoes 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 2.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. Skill RefresherFormulas
Auditing FormulasExcel provides a few tools that you can use to review the formulas entered into a worksheet. For example, instead of showing the outputs for the formulas used in a worksheet, you can have Excel show the formula as it was entered in the cell locations. This is demonstrated as follows:
Figure 2.8 shows the Budget Detail worksheet after activating the Show Formulas command in the Formulas tab of the Ribbon. As shown in the figure, this command allows you to view and check all the formulas in a worksheet without having to click each cell individually. After activating this command, the column widths in your worksheet increase significantly. The column widths were adjusted for the worksheet shown in Figure 2.8 so all columns can be seen. The column widths return to their previous width when the Show Formulas command is deactivated. Figure 2.8 Show Formulas CommandSkill RefresherShow Formulas
Keyboard ShortcutsShow Formulas
Two other tools in the Formula Auditing group of commands are the Trace Precedents and Trace Dependents commands. These commands are used to trace the cell references used in a formula. A precedent cell is a cell whose value is used in other cells. The Trace Precedents command shows an arrow to indicate the cells or ranges (precedents) which affect the active cell’s value. A dependent cell is a cell whose value depends on the values of other cells in the workbook. The Trace Dependents command shows where any given cell is referenced in a formula. The following is a demonstration of these commands:
Figure 2.9 shows the Trace Dependents arrow on the Budget Detail worksheet. The blue dot represents the activated cell. The arrows indicate where the cell is referenced in formulas. Figure 2.9 Trace Dependents ExampleFigure 2.10 shows the Trace Precedents arrow on the Budget Detail worksheet. The blue dots on this arrow indicate the cells that are referenced in the formula contained in the activated cell. The arrow is pointing to the activated cell location that contains the formula. Figure 2.10 Trace Precedents ExampleSkill RefresherTrace Dependents
Skill RefresherTrace Precedents
Key Takeaways
AttributionAdapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0. In what order does Excel process formula?When evaluating a formula, Excel follows a standard math protocol called "order of operations". In general, Excel's order of operation follows the acronym PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) but with some customization to handle the formula syntax in a spreadsheet.
How MS Excel evaluates a complex formula?Select the cell that you want to evaluate. Only one cell can be evaluated at a time. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula. Click Evaluate to examine the value of the underlined reference.
Which mathematical operation is calculated first in a complex equation?Because the division operation comes before the multiplication, it is calculated first: 3/4=0.75.
How can you force a certain order of operations in a formula?How can you force a certain order of operations in a formula? You can enter a cell references in a formula by typing in the reference. You can enter a cell reference in a formula by clicking the cell location on the spreadsheet. Cell references make it easy to use values from other cells in a formula.
|