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.
Introduction
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 1 Completed Personal Cash Budget Workbook 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 1 Completed Personal Cash Budget Workbook
This text was adapted by Saylor Academy under
a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work's original creator or licensor.