This multiplies the monthly lease payments by 12 to calculate the total payments required for the year. Click cell B6 in the Car Lease Payments worksheet.Click the Car Lease Payments worksheet tab.Click cell C5 on the Budget Summary worksheet.The value of multiplying the monthly mortgage payments by 12 is now displayed on the Budget Summary worksheet. The formula in the formula bar should read: =’Mortgage Payments’!B5*12 This multiplies the monthly payments by 12 to calculate the total payments required for the year. Click cell B5 in the Mortgage Payments worksheet.Click the Mortgage Payments worksheet tab.Click cell C4 in the Budget Summary worksheet.The following steps explain how this is accomplished: As a result, we will create a formula that references cell locations in the Mortgage Payments and Car Lease Payments worksheets. In the Budget Summary worksheet, we need to show the total annual payments. However, you will recall that we used the PMT function to calculate the monthly payments. Figure 9.3.1 Cell Reference Showing the Total Expenses in the Budget Summary WorksheetĪs shown in Figure 9.3.1, the Budget Summary worksheet is designed to show the expense budget for the mortgage payments and the auto lease payments. Notice that the cell reference D12 is preceded by the Budget Detail worksheet name enclosed in apostrophes followed by an exclamation point (‘Budget Detail’!) This indicates that the value displayed in the cell is referencing a cell location in the Budget Detail worksheet. The output of the SUM function in cell D12 on the Budget Detail worksheet will be displayed in cell C3 on the Budget Summary worksheet.įigure 9.3.1 shows how the cell reference appears in the Budget Summary worksheet. Click cell D12 on the Budget Detail worksheet.Click cell C3 in the Budget Summary worksheet.Outputs from the formulas and functions that were entered into the Budget Detail, Mortgage Payments, and Car Lease Payments worksheets will be displayed on the Budget Summary worksheet through the use of cell references. This is how data will be displayed on the Budget Summary worksheet in the Personal Budget workbook. Cell references can also be used to display values or the outputs of formulas and functions in cell locations on other worksheets. So far we have used cell references in formulas and functions, which allow Excel to produce new outputs when the values in the cell references are changed. The result is a number like 36, 18, 12.9, etc.9.3 Creating a Budget Summary Worksheet Linking Worksheets (Creating a Summary Worksheet) If you want instead a simple number without a percent sign, just multiply by 100: =(C6/total)*100 To display a percentage in Excel, use the Percentage number format, which will automatically display a decimal value as a percentage. To format a number like this as a percentage with the percent sign (%), apply the Percentage number format. In cell D6, the ratio is 700/1945, which is approximately 0.36. The numbers in column D are decimal values that express a ratio. The formula becomes: =C6/$C$15 // returns 0.3599Īs the formula is copied down, we get a percent of total for each item shown in the table. Using the named range total (C15). Without the named range, we need to use an absolute reference to "lock" the address to C15 so the reference doesn't change as the formula is copied down column D. The formula in D6, copied down, is: =C6/total // returns 0.3599 In cell D6, the ratio is 700/1945, which is approximately 0.36 (36% when formatted as a percentage). To calculate the "percent of total" for a given expense, we need to divide the amount of the expense by the total of all expenses. For example, 85% is read as "Eighty-five percent" and is equivalent to 85/100 or 0.85. In mathematics, a percentage is a number expressed as a fraction of 100. The total already exists in the named range total (C15) which contains a formula based on the SUM function: =SUM(C6:C14) In other words, given that we know the total is $1945, and we know Rent is $700, we want to determine that Rent is 36% of the total. In this example, the goal is to work out the "percent of total" for each expense shown in the worksheet.
0 Comments
Leave a Reply. |