{"id":109,"date":"2016-10-20T23:51:23","date_gmt":"2016-10-20T23:51:23","guid":{"rendered":"https:\/\/pressbooks.usnh.edu\/math502\/chapter\/formulas\/"},"modified":"2023-03-24T14:25:59","modified_gmt":"2023-03-24T14:25:59","slug":"formulas","status":"publish","type":"chapter","link":"https:\/\/pressbooks.usnh.edu\/mth402\/chapter\/formulas\/","title":{"raw":"Formulas","rendered":"Formulas"},"content":{"raw":"<div class=\"2.1-formulas\">\n<div class=\"textbox learning-objectives\">\n<h3>Learning Objectives<\/h3>\n<ul>\n \t<li>Learn how to create basic formulas.<\/li>\n \t<li>Understand relative referencing when copying and pasting formulas.<\/li>\n \t<li>Work with complex formulas by controlling the order of mathematical operations.<\/li>\n<\/ul>\n<\/div>\nThis 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.\n\n<strong>Figure 2.51<\/strong> shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains four worksheets. The first worksheet,\u00a0<strong>Budget Summary<\/strong>, contains formulas that utilize or reference the data in the other three worksheets. As a result, the\u00a0<strong>Budget Summary<\/strong>\u00a0worksheet serves as an overview of the data that was entered and calculated in the other three worksheets of the workbook.\n\n[caption id=\"\" align=\"aligncenter\" width=\"843\"]<a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image1-8.png\"><img src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2016\/10\/image1-8.png\" alt=\"Completed personal budget workbook open to Budget Summary worksheet.\" width=\"843\" height=\"525\"><\/a> Figure 2.51 Completed Personal Budget Workbook[\/caption]\n<h2>Creating a Basic Formula<\/h2>\n<em>If you wish to follow along, download Data File: <a href=\"https:\/\/granite.pressbooks.pub\/app\/uploads\/sites\/30\/2019\/10\/CH2-Data.xlsx#fixme\">CH2 Data<\/a>\n<\/em>\n\nFormulas\u00a0are 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\u00a0cell\u00a0references. 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.\n\nTo demonstrate the construction of a basic formula, we will begin working on the <strong>Budget Detail<\/strong> worksheet in the Personal Budget workbook, which is shown in <strong>Figure 2.52<\/strong>. To complete this worksheet, we will add several formulas and functions. <strong>Table 2.51 <\/strong>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 <strong>Table 2.51<\/strong>\u00a0to understand how we are defining these categories before proceeding.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1316\"]<a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image2-50.jpg\"><img src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image2-50.jpg\" alt=\"Budget workbook open to budget detail worksheet. Column titles are Category, Percent of Total, Monthly Spend, Annual Spend, LY (Last Year), and Percent Change. Formulas and functions will be added in the blank cells in Columns B, C, and F to produce mathematical outputs for this worksheet.\" width=\"1316\" height=\"806\"><\/a> Figure 2.52 Budget Detail Worksheet[\/caption]\n\nTable 2.51 Spend Category Definitions\n<table><colgroup><\/colgroup>\n<thead>\n<tr>\n<td>Category<\/td>\n<td>Definition<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><strong>Household Utilities<\/strong><\/td>\n<td>Money spent on electricity, heat, and water and on cable, phone, and Internet access<\/td>\n<\/tr>\n<tr>\n<td><strong>Food<\/strong><\/td>\n<td>Money spent on groceries, toiletries, and related items<\/td>\n<\/tr>\n<tr>\n<td><strong>Gasoline<\/strong><\/td>\n<td>Money spent on fuel for automobiles<\/td>\n<\/tr>\n<tr>\n<td><strong>Clothes<\/strong><\/td>\n<td>Money spent on clothes, shoes, and accessories<\/td>\n<\/tr>\n<tr>\n<td><strong>Insurance<\/strong><\/td>\n<td>Money spent on homeowner\u2019s or automobile insurance<\/td>\n<\/tr>\n<tr>\n<td><strong>Taxes<\/strong><\/td>\n<td>Money spent on school and property taxes (this example of the personal budget assumes that we own property).<\/td>\n<\/tr>\n<tr>\n<td><strong>Entertainment<\/strong><\/td>\n<td>Money spent on entertainment, including dining out, movie and theater tickets, parties, and so on<\/td>\n<\/tr>\n<tr>\n<td><strong>Vacation<\/strong><\/td>\n<td>Money spent on vacations<\/td>\n<\/tr>\n<tr>\n<td><strong>Miscellaneous<\/strong><\/td>\n<td>Includes any other spending categories, such as textbooks, software, journals, school or work supplies, and so on<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nThe first formula that we will add to the\u00a0<strong>Budget Detail<\/strong>\u00a0worksheet 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:\n<ol>\n \t<li>Open the Data file named <strong>CH2 Data<\/strong> and use the File\/Save As command to save it with the new name <strong>CH2 Personal Budget<\/strong>.<\/li>\n \t<li>Click the\u00a0<strong>Budget Detail<\/strong>\u00a0worksheet tab to open the worksheet.<\/li>\n \t<li>Click cell C3.<\/li>\n \t<li>Type an equal sign <strong>=<\/strong>. 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.<\/li>\n \t<li>Type\u00a0<strong>D3<\/strong>. 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.<\/li>\n \t<li>Type the slash symbol <strong>\/<\/strong>. This is the symbol for division in Excel. As shown in <strong>Table 2.52<\/strong> the mathematical operators in Excel are slightly different from those found on a typical calculator.<\/li>\n \t<li>Type the number\u00a0<strong>12<\/strong>. This divides the value in cell D3 by 12. In this formula, a number, or\u00a0constant, is used instead of a cell reference because it will not change. In other words, there will always be 12 months in a year.<\/li>\n \t<li>Press the ENTER key.<\/li>\n<\/ol>\nTable 2.2\u00a0Excel Mathematical Operators\n<table style=\"height: 78px;\"><colgroup><\/colgroup>\n<thead>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">Symbol<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Operation<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">+<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Addition<\/td>\n<\/tr>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">\u2212<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Subtraction<\/td>\n<\/tr>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">\/<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Division<\/td>\n<\/tr>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">*<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Multiplication<\/td>\n<\/tr>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">^<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Power\/Exponent<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"textbox why\">\n<h3>Why?<\/h3>\n<strong>Use Cell References<\/strong>\n\nCell 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,\u00a0you should never use a calculator to determine a mathematical output and type it into the cell location of a worksheet. Doing so eliminates Excel\u2019s cell-referencing benefits as well as your ability to trace a formula to determine how outputs are being produced.\n\n<\/div>\n<strong>Figure 2.35<\/strong> shows how the formula appears in cell C3 before you press the ENTER key. <strong>Figure 2.54<\/strong> 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1263\"]<a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image3-2.jpeg\"><img src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image3-2.jpeg\" alt=\"Formula is added to C3 as &quot;=D3\/12&quot;. Referenced cell has a blue border.\" width=\"1263\" height=\"688\"><\/a> Figure 2.53 Adding a Formula to a Worksheet[\/caption]\n\n[caption id=\"\" align=\"aligncenter\" width=\"957\"]<a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image4-3.jpeg\"><img src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image4-3.jpeg\" alt=\"Formula has been added to cell and shows in function bar, but cell itself displays output.\" width=\"957\" height=\"475\"><\/a> Figure 2.54 Formula Output for Monthly Spend[\/caption]\n\n&nbsp;\n<div class=\"textbox why\">\n<h3>Why?<\/h3>\n<strong>Use Universal Constants<\/strong>\n\nIf 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.\u00a0Do not type the values that exist in cell locations into an Excel formula. This will eliminate Excel\u2019s 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.\n\n<\/div>\n<h2>Relative References (Copying and Pasting Formulas)<\/h2>\nOnce a formula is typed into a worksheet, it can be copied and pasted to other cell locations. For example, <strong>Figure 2.54<\/strong> 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\u00a0relative\u00a0referencing\u00a0and is demonstrated as follows:\n<ol>\n \t<li>Click cell C3.<\/li>\n \t<li>Place the mouse pointer over the Auto Fill Handle.<\/li>\n \t<li>When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell C11. This pastes the formula into the range C4:C11.<\/li>\n \t<li>Double click cell C6. Notice that the cell reference in the formula is automatically changed to D6.<\/li>\n \t<li>Press the ENTER key.<\/li>\n<\/ol>\n<strong>Figure 2.55<\/strong> 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.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1321\"]<a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image5-2.jpg\"><img src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image5-2.jpg\" alt=\"Cell D6 formula changed automatically when autofill was used due to relative referencing.\" width=\"1321\" height=\"673\"><\/a> Figure 2.55 Relative Reference Example[\/caption]\n\n&nbsp;\n<div class=\"textbox why\">\n<h3>Why?<\/h3>\n<strong>Use Relative Referencing<\/strong>\n\nRelative 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.\n\n<\/div>\n<h2>Creating Complex Formulas (Controlling the Order of Operations)<\/h2>\nThe 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. <strong>Table 2.53<\/strong> 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:\n<ol>\n \t<li>Click cell F3 in the\u00a0<strong>Budget Detail<\/strong>\u00a0worksheet.<\/li>\n \t<li>Type an equal sign <strong>=<\/strong>.<\/li>\n \t<li>Type an open parenthesis <strong>(<\/strong>.<\/li>\n \t<li>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.<\/li>\n \t<li>Type a minus sign <strong>\u2212<\/strong>.<\/li>\n \t<li>Click cell E3 to add this cell reference to the formula.<\/li>\n \t<li>Type a closing parenthesis <strong>)<\/strong>.<\/li>\n \t<li>Type the slash <strong>\/<\/strong>\u00a0symbol for division.<\/li>\n \t<li>Click cell E3. This completes the formula that will calculate the percent change of last year\u2019s actual spent dollars vs. this year\u2019s budgeted spend dollars (<strong>see Figure 2.6<\/strong>).<\/li>\n \t<li>Press the ENTER key.<\/li>\n \t<li>Click cell F3 to activate it.<\/li>\n \t<li>Place the mouse pointer over the Auto Fill Handle.<\/li>\n \t<li>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.<\/li>\n<\/ol>\nTable 2.53 Standard Order of Mathematical Operations\n<table><colgroup><\/colgroup>\n<thead>\n<tr>\n<td>Symbol<\/td>\n<td>Order<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>( )<\/td>\n<td>Override Standard Order:\u00a0Any 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.<\/td>\n<\/tr>\n<tr>\n<td>^<\/td>\n<td>First:\u00a0Excel executes any exponential computations first.<\/td>\n<\/tr>\n<tr>\n<td>* or \/<\/td>\n<td>Second:\u00a0Excel 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.<\/td>\n<\/tr>\n<tr>\n<td>+ or \u2212<\/td>\n<td>Third:\u00a0Excel performs any addition or subtraction computations third. When there are multiple instances of these computations in a formula, they are executed in order from left to right.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<strong>Figure 2.56<\/strong> shows the formula that was added to the\u00a0<strong>Budget Detail\u00a0<\/strong>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 <strong>Table 2.53)<\/strong>. In this case, if parentheses were not used, Excel would produce an erroneous result for this worksheet.\n\n[caption id=\"\" align=\"aligncenter\" width=\"1319\"]<a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image6-2.jpg\"><img src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image6-2.jpg\" alt=\"Mathematical computations in parentheses are performed first.\" width=\"1319\" height=\"627\"><\/a> Figure 2.56 Adding the Percent Change Formula[\/caption]\n\n<strong>Figure 2.57<\/strong> 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\u22121), which is 2,999. Since cell F3 is formatted as a percentage, Excel expresses the output as an increase of 299900%.\n\n[caption id=\"\" align=\"alignnone\" width=\"1320\"]<a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image7-2.jpg\"><img src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image7-2.jpg\" alt=\"Without parentheses, calculation is performed according to order of operations. Poorly constructed formulas can result in erroneous results.\" width=\"1320\" height=\"417\"><\/a> Figure 2.57 Removing the Parentheses from the Percent Change Formula[\/caption]\n\n&nbsp;\n<div class=\"textbox integrity\">\n<h3>Integrity Check<\/h3>\n<strong>Does the Output of Your Formula Make Sense?<\/strong>\n\nIt 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 <strong>Figure 2.57<\/strong>, 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.\n\n<\/div>\n<div class=\"textbox examples\">\n<h3>Skill Refresher<\/h3>\n<strong>Formulas<\/strong>\n<ol>\n \t<li>Type an equal sign <strong>=<\/strong>.<\/li>\n \t<li>Click or type a cell location. If using constants, type a number.<\/li>\n \t<li>Type a mathematical operator.<\/li>\n \t<li>Click or type a cell location. If using constants, type a number.<\/li>\n \t<li>Use parentheses where necessary to control the order of operations.<\/li>\n \t<li>Press the ENTER key<\/li>\n<\/ol>\n<\/div>\n<div class=\"textbox key-takeaways\">\n<h3>Key Takeaways<\/h3>\n<ul>\n \t<li>Mathematical computations are conducted through formulas and functions.<\/li>\n \t<li>An equal sign <strong>=<\/strong> precedes all formulas and functions.<\/li>\n \t<li>Formulas and functions must be created with cell references to conduct what-if scenarios where mathematical outputs are recalculated when one or more inputs are changed.<\/li>\n \t<li>Mathematical operators on a typical calculator are different from those used in Excel. <strong>Table 2.2<\/strong> \"Excel Mathematical Operators\"\u00a0lists Excel mathematical operators.<\/li>\n \t<li>When using numerical values in formulas and functions, only use universal constants that do not change, such as days in a week, months in a year, and so on.<\/li>\n \t<li>Relative referencing automatically adjusts the cell references in formulas and functions when they are pasted into new locations on a worksheet. This eliminates the need to retype formulas and functions when they are needed in multiple rows or columns on a worksheet.<\/li>\n \t<li>Parentheses must be used to control the order of operations when necessary for complex formulas.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n<h2>Attribution<\/h2>\nAdapted by Mary Schatz from <a href=\"https:\/\/saylordotorg.github.io\/text_how-to-use-microsoft-excel-v1.1\/index.html\" target=\"_blank\" rel=\"noopener noreferrer\">How to Use Microsoft Excel: The Careers in Practice Series<\/a>,\u00a0adapted by <a href=\"https:\/\/www.saylor.org\" target=\"_blank\" rel=\"noopener noreferrer\">The Saylor Foundation<\/a> without attribution as requested by the work\u2019s original creator or licensee,\u00a0and licensed under <a href=\"http:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\" target=\"_blank\" rel=\"noopener noreferrer\">CC BY-NC-SA 3.0<\/a>.","rendered":"<div class=\"2.1-formulas\">\n<div class=\"textbox learning-objectives\">\n<h3>Learning Objectives<\/h3>\n<ul>\n<li>Learn how to create basic formulas.<\/li>\n<li>Understand relative referencing when copying and pasting formulas.<\/li>\n<li>Work with complex formulas by controlling the order of mathematical operations.<\/li>\n<\/ul>\n<\/div>\n<p>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.<\/p>\n<p><strong>Figure 2.51<\/strong> shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains four worksheets. The first worksheet,\u00a0<strong>Budget Summary<\/strong>, contains formulas that utilize or reference the data in the other three worksheets. As a result, the\u00a0<strong>Budget Summary<\/strong>\u00a0worksheet serves as an overview of the data that was entered and calculated in the other three worksheets of the workbook.<\/p>\n<figure style=\"width: 843px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image1-8.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2016\/10\/image1-8.png\" alt=\"Completed personal budget workbook open to Budget Summary worksheet.\" width=\"843\" height=\"525\" \/><\/a><figcaption class=\"wp-caption-text\">Figure 2.51 Completed Personal Budget Workbook<\/figcaption><\/figure>\n<h2>Creating a Basic Formula<\/h2>\n<p><em>If you wish to follow along, download Data File: <a href=\"https:\/\/granite.pressbooks.pub\/app\/uploads\/sites\/30\/2019\/10\/CH2-Data.xlsx#fixme\">CH2 Data<\/a><br \/>\n<\/em><\/p>\n<p>Formulas\u00a0are 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\u00a0cell\u00a0references. 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.<\/p>\n<p>To demonstrate the construction of a basic formula, we will begin working on the <strong>Budget Detail<\/strong> worksheet in the Personal Budget workbook, which is shown in <strong>Figure 2.52<\/strong>. To complete this worksheet, we will add several formulas and functions. <strong>Table 2.51 <\/strong>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 <strong>Table 2.51<\/strong>\u00a0to understand how we are defining these categories before proceeding.<\/p>\n<figure style=\"width: 1316px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image2-50.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image2-50.jpg\" alt=\"Budget workbook open to budget detail worksheet. Column titles are Category, Percent of Total, Monthly Spend, Annual Spend, LY (Last Year), and Percent Change. Formulas and functions will be added in the blank cells in Columns B, C, and F to produce mathematical outputs for this worksheet.\" width=\"1316\" height=\"806\" \/><\/a><figcaption class=\"wp-caption-text\">Figure 2.52 Budget Detail Worksheet<\/figcaption><\/figure>\n<p>Table 2.51 Spend Category Definitions<\/p>\n<table>\n<colgroup><\/colgroup>\n<thead>\n<tr>\n<td>Category<\/td>\n<td>Definition<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><strong>Household Utilities<\/strong><\/td>\n<td>Money spent on electricity, heat, and water and on cable, phone, and Internet access<\/td>\n<\/tr>\n<tr>\n<td><strong>Food<\/strong><\/td>\n<td>Money spent on groceries, toiletries, and related items<\/td>\n<\/tr>\n<tr>\n<td><strong>Gasoline<\/strong><\/td>\n<td>Money spent on fuel for automobiles<\/td>\n<\/tr>\n<tr>\n<td><strong>Clothes<\/strong><\/td>\n<td>Money spent on clothes, shoes, and accessories<\/td>\n<\/tr>\n<tr>\n<td><strong>Insurance<\/strong><\/td>\n<td>Money spent on homeowner\u2019s or automobile insurance<\/td>\n<\/tr>\n<tr>\n<td><strong>Taxes<\/strong><\/td>\n<td>Money spent on school and property taxes (this example of the personal budget assumes that we own property).<\/td>\n<\/tr>\n<tr>\n<td><strong>Entertainment<\/strong><\/td>\n<td>Money spent on entertainment, including dining out, movie and theater tickets, parties, and so on<\/td>\n<\/tr>\n<tr>\n<td><strong>Vacation<\/strong><\/td>\n<td>Money spent on vacations<\/td>\n<\/tr>\n<tr>\n<td><strong>Miscellaneous<\/strong><\/td>\n<td>Includes any other spending categories, such as textbooks, software, journals, school or work supplies, and so on<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The first formula that we will add to the\u00a0<strong>Budget Detail<\/strong>\u00a0worksheet 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:<\/p>\n<ol>\n<li>Open the Data file named <strong>CH2 Data<\/strong> and use the File\/Save As command to save it with the new name <strong>CH2 Personal Budget<\/strong>.<\/li>\n<li>Click the\u00a0<strong>Budget Detail<\/strong>\u00a0worksheet tab to open the worksheet.<\/li>\n<li>Click cell C3.<\/li>\n<li>Type an equal sign <strong>=<\/strong>. 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.<\/li>\n<li>Type\u00a0<strong>D3<\/strong>. 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.<\/li>\n<li>Type the slash symbol <strong>\/<\/strong>. This is the symbol for division in Excel. As shown in <strong>Table 2.52<\/strong> the mathematical operators in Excel are slightly different from those found on a typical calculator.<\/li>\n<li>Type the number\u00a0<strong>12<\/strong>. This divides the value in cell D3 by 12. In this formula, a number, or\u00a0constant, is used instead of a cell reference because it will not change. In other words, there will always be 12 months in a year.<\/li>\n<li>Press the ENTER key.<\/li>\n<\/ol>\n<p>Table 2.2\u00a0Excel Mathematical Operators<\/p>\n<table style=\"height: 78px;\">\n<colgroup><\/colgroup>\n<thead>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">Symbol<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Operation<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">+<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Addition<\/td>\n<\/tr>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">\u2212<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Subtraction<\/td>\n<\/tr>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">\/<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Division<\/td>\n<\/tr>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">*<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Multiplication<\/td>\n<\/tr>\n<tr style=\"height: 13px;\">\n<td style=\"height: 13px; width: 117.049px;\">^<\/td>\n<td style=\"height: 13px; width: 245.938px;\">Power\/Exponent<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"textbox why\">\n<h3>Why?<\/h3>\n<p><strong>Use Cell References<\/strong><\/p>\n<p>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,\u00a0you should never use a calculator to determine a mathematical output and type it into the cell location of a worksheet. Doing so eliminates Excel\u2019s cell-referencing benefits as well as your ability to trace a formula to determine how outputs are being produced.<\/p>\n<\/div>\n<p><strong>Figure 2.35<\/strong> shows how the formula appears in cell C3 before you press the ENTER key. <strong>Figure 2.54<\/strong> 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.<\/p>\n<figure style=\"width: 1263px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image3-2.jpeg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image3-2.jpeg\" alt=\"Formula is added to C3 as &quot;=D3\/12&quot;. Referenced cell has a blue border.\" width=\"1263\" height=\"688\" \/><\/a><figcaption class=\"wp-caption-text\">Figure 2.53 Adding a Formula to a Worksheet<\/figcaption><\/figure>\n<figure style=\"width: 957px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image4-3.jpeg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image4-3.jpeg\" alt=\"Formula has been added to cell and shows in function bar, but cell itself displays output.\" width=\"957\" height=\"475\" \/><\/a><figcaption class=\"wp-caption-text\">Figure 2.54 Formula Output for Monthly Spend<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<div class=\"textbox why\">\n<h3>Why?<\/h3>\n<p><strong>Use Universal Constants<\/strong><\/p>\n<p>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.\u00a0Do not type the values that exist in cell locations into an Excel formula. This will eliminate Excel\u2019s 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.<\/p>\n<\/div>\n<h2>Relative References (Copying and Pasting Formulas)<\/h2>\n<p>Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations. For example, <strong>Figure 2.54<\/strong> 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\u00a0relative\u00a0referencing\u00a0and is demonstrated as follows:<\/p>\n<ol>\n<li>Click cell C3.<\/li>\n<li>Place the mouse pointer over the Auto Fill Handle.<\/li>\n<li>When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell C11. This pastes the formula into the range C4:C11.<\/li>\n<li>Double click cell C6. Notice that the cell reference in the formula is automatically changed to D6.<\/li>\n<li>Press the ENTER key.<\/li>\n<\/ol>\n<p><strong>Figure 2.55<\/strong> 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.<\/p>\n<figure style=\"width: 1321px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image5-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image5-2.jpg\" alt=\"Cell D6 formula changed automatically when autofill was used due to relative referencing.\" width=\"1321\" height=\"673\" \/><\/a><figcaption class=\"wp-caption-text\">Figure 2.55 Relative Reference Example<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<div class=\"textbox why\">\n<h3>Why?<\/h3>\n<p><strong>Use Relative Referencing<\/strong><\/p>\n<p>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.<\/p>\n<\/div>\n<h2>Creating Complex Formulas (Controlling the Order of Operations)<\/h2>\n<p>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. <strong>Table 2.53<\/strong> 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:<\/p>\n<ol>\n<li>Click cell F3 in the\u00a0<strong>Budget Detail<\/strong>\u00a0worksheet.<\/li>\n<li>Type an equal sign <strong>=<\/strong>.<\/li>\n<li>Type an open parenthesis <strong>(<\/strong>.<\/li>\n<li>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.<\/li>\n<li>Type a minus sign <strong>\u2212<\/strong>.<\/li>\n<li>Click cell E3 to add this cell reference to the formula.<\/li>\n<li>Type a closing parenthesis <strong>)<\/strong>.<\/li>\n<li>Type the slash <strong>\/<\/strong>\u00a0symbol for division.<\/li>\n<li>Click cell E3. This completes the formula that will calculate the percent change of last year\u2019s actual spent dollars vs. this year\u2019s budgeted spend dollars (<strong>see Figure 2.6<\/strong>).<\/li>\n<li>Press the ENTER key.<\/li>\n<li>Click cell F3 to activate it.<\/li>\n<li>Place the mouse pointer over the Auto Fill Handle.<\/li>\n<li>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.<\/li>\n<\/ol>\n<p>Table 2.53 Standard Order of Mathematical Operations<\/p>\n<table>\n<colgroup><\/colgroup>\n<thead>\n<tr>\n<td>Symbol<\/td>\n<td>Order<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>( )<\/td>\n<td>Override Standard Order:\u00a0Any 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.<\/td>\n<\/tr>\n<tr>\n<td>^<\/td>\n<td>First:\u00a0Excel executes any exponential computations first.<\/td>\n<\/tr>\n<tr>\n<td>* or \/<\/td>\n<td>Second:\u00a0Excel 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.<\/td>\n<\/tr>\n<tr>\n<td>+ or \u2212<\/td>\n<td>Third:\u00a0Excel performs any addition or subtraction computations third. When there are multiple instances of these computations in a formula, they are executed in order from left to right.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Figure 2.56<\/strong> shows the formula that was added to the\u00a0<strong>Budget Detail\u00a0<\/strong>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 <strong>Table 2.53)<\/strong>. In this case, if parentheses were not used, Excel would produce an erroneous result for this worksheet.<\/p>\n<figure style=\"width: 1319px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image6-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image6-2.jpg\" alt=\"Mathematical computations in parentheses are performed first.\" width=\"1319\" height=\"627\" \/><\/a><figcaption class=\"wp-caption-text\">Figure 2.56 Adding the Percent Change Formula<\/figcaption><\/figure>\n<p><strong>Figure 2.57<\/strong> 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\u22121), which is 2,999. Since cell F3 is formatted as a percentage, Excel expresses the output as an increase of 299900%.<\/p>\n<figure style=\"width: 1320px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/openoregon.pressbooks.pub\/app\/uploads\/sites\/7\/2016\/09\/image7-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/pressbooks.usnh.edu\/math502\/wp-content\/blogs.dir\/12\/files\/2023\/03\/image7-2.jpg\" alt=\"Without parentheses, calculation is performed according to order of operations. Poorly constructed formulas can result in erroneous results.\" width=\"1320\" height=\"417\" \/><\/a><figcaption class=\"wp-caption-text\">Figure 2.57 Removing the Parentheses from the Percent Change Formula<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<div class=\"textbox integrity\">\n<h3>Integrity Check<\/h3>\n<p><strong>Does the Output of Your Formula Make Sense?<\/strong><\/p>\n<p>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 <strong>Figure 2.57<\/strong>, 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.<\/p>\n<\/div>\n<div class=\"textbox examples\">\n<h3>Skill Refresher<\/h3>\n<p><strong>Formulas<\/strong><\/p>\n<ol>\n<li>Type an equal sign <strong>=<\/strong>.<\/li>\n<li>Click or type a cell location. If using constants, type a number.<\/li>\n<li>Type a mathematical operator.<\/li>\n<li>Click or type a cell location. If using constants, type a number.<\/li>\n<li>Use parentheses where necessary to control the order of operations.<\/li>\n<li>Press the ENTER key<\/li>\n<\/ol>\n<\/div>\n<div class=\"textbox key-takeaways\">\n<h3>Key Takeaways<\/h3>\n<ul>\n<li>Mathematical computations are conducted through formulas and functions.<\/li>\n<li>An equal sign <strong>=<\/strong> precedes all formulas and functions.<\/li>\n<li>Formulas and functions must be created with cell references to conduct what-if scenarios where mathematical outputs are recalculated when one or more inputs are changed.<\/li>\n<li>Mathematical operators on a typical calculator are different from those used in Excel. <strong>Table 2.2<\/strong> &#8220;Excel Mathematical Operators&#8221;\u00a0lists Excel mathematical operators.<\/li>\n<li>When using numerical values in formulas and functions, only use universal constants that do not change, such as days in a week, months in a year, and so on.<\/li>\n<li>Relative referencing automatically adjusts the cell references in formulas and functions when they are pasted into new locations on a worksheet. This eliminates the need to retype formulas and functions when they are needed in multiple rows or columns on a worksheet.<\/li>\n<li>Parentheses must be used to control the order of operations when necessary for complex formulas.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n<h2>Attribution<\/h2>\n<p>Adapted by Mary Schatz from <a href=\"https:\/\/saylordotorg.github.io\/text_how-to-use-microsoft-excel-v1.1\/index.html\" target=\"_blank\" rel=\"noopener noreferrer\">How to Use Microsoft Excel: The Careers in Practice Series<\/a>,\u00a0adapted by <a href=\"https:\/\/www.saylor.org\" target=\"_blank\" rel=\"noopener noreferrer\">The Saylor Foundation<\/a> without attribution as requested by the work\u2019s original creator or licensee,\u00a0and licensed under <a href=\"http:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\" target=\"_blank\" rel=\"noopener noreferrer\">CC BY-NC-SA 3.0<\/a>.<\/p>\n","protected":false},"author":11,"menu_order":5,"template":"","meta":{"pb_show_title":"","pb_short_title":"","pb_subtitle":"","pb_authors":["noreen-brown","barbara-lave","julie-romey","mary-schatz","diane-shingledecker"],"pb_section_license":"cc-by"},"chapter-type":[49],"contributor":[63,66,64,65,62],"license":[53],"class_list":["post-109","chapter","type-chapter","status-publish","hentry","chapter-type-numberless","contributor-barbara-lave","contributor-diane-shingledecker","contributor-julie-romey","contributor-mary-schatz","contributor-noreen-brown","license-cc-by"],"part":30,"_links":{"self":[{"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/pressbooks\/v2\/chapters\/109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/wp\/v2\/users\/11"}],"version-history":[{"count":1,"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/pressbooks\/v2\/chapters\/109\/revisions"}],"predecessor-version":[{"id":110,"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/pressbooks\/v2\/chapters\/109\/revisions\/110"}],"part":[{"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/pressbooks\/v2\/parts\/30"}],"metadata":[{"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/pressbooks\/v2\/chapters\/109\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/wp\/v2\/media?parent=109"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/pressbooks\/v2\/chapter-type?post=109"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/wp\/v2\/contributor?post=109"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/mth402\/wp-json\/wp\/v2\/license?post=109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}