Enter a formula that references the value in cell f10

EX 115

Working with Formulas and Functions Developing a Family Budget

Case | Drake Family Budget Newlyweds Diane and Glenn Drake are trying to balance career, school, and family life. The couple’s income and expenses vary throughout the year. Diane works full-time as a legal assistant, but earns less in the summer months when she cuts back her work hours to attend summer courses. Glenn is in a graduate program at a nearby university where he recently was hired as a lab assis- tant. In the summer, Glenn takes on other work to earn additional income. The couple just moved into a new apartment, but plan to purchase a house in the next several few years. Although Glenn and Diane’s salaries have grown in the past few years, the couple seems to have less cash on hand. This fi nancial shortage has prompted them to take a closer look at their fi nances and fi gure out how to best manage them.

Diane has set up an Excel workbook and entered the monthly income from their two jobs. She has identifi ed and entered expenses the family pays on a monthly basis, such as the rent and grocery bill, as well as other expenses that occur only a few times a year, such as Glenn’s tuition and vacations. She wants to calculate how much money they are bringing in and how much money they are spending. She also wants to come up with a savings plan for the down payment on a house they hope to buy in a few years.

You’ll help Diane complete the workbook. You will enter formulas to perform the calculations that will provide Diane with a better overall picture of the family’s fi nances. Diane and Glenn will be able to use this information to manage their money more effectively.

OBJECTIVES

Session 3.1 • Copy formulas • Build formulas containing

relative, absolute, and mixed references

• Review function syntax • Insert a function with the Insert

Function dialog box • Search for a function • Type a function directly in

a cell

Session 3.2 • Use AutoFill to fi ll in a formula

and complete a series • Enter the IF logical function • Insert the date with the TODAY

function • Use the PMT fi nancial function

to calculate monthly mortgage payments

TUTORIAL 3

STARTING DATA FILES

Excel3 Tutorial

Drake.xlsx

Review

Timov.xlsx

Case1

Chemistry.xlsx Wizard.xlsx

Case2

Loan.xlsx

Case3

V6.xlsx

Case4

C_C7445_EX03.4c.indd 115C_C7445_EX03.4c.indd 115 6/8/10 2:48 PM6/8/10 2:48 PM

EX 116 Excel | Tutorial 3 Working with Formulas and Functions

SESSION 3.1 VISUAL OVERVIEW

Functions are organized by the category in the Function Library goup. Select a function to open the Funcion Arguments dialog box.

The Insert Function button opens the Insert Function dialog box from which you can select a function.

An absolute reference remains fixed when the cell formula is copied to a new location, and has $ in front of the column letter and the row number. $D$5 is the absolute reference to cell D5.

A relative reference is interpreted in relation to the location of the cell containing the formula. L19 is the relative reference to cell L19.

C_C7445_EX03.4c.indd 116C_C7445_EX03.4c.indd 116 6/8/10 2:48 PM6/8/10 2:48 PM

EX 117Tutorial 3 Working with Formulas and Functions | Excel

Required arguments, in bold, are needed for the function to return a value.

The SUM function returns the sum of the values in the range.

The MIN function returns the minimum value in the range.

The AVERAGE function returns the average value of the range.

The MAX function returns the maximum value in the range.

A mixed reference contains an absolute row or an absolute column. This relative reference could be changed to the mixed reference $N20 or N$20.

The Function Arguments dialog box displays the arguments used by each function. Arguments are the numbers, text, or cell references used by the function to return a value.

Optional arguments are not required for the function to return a value.

CELL REFERENCES AND EXCEL FUNCTIONS

C_C7445_EX03.4c.indd 117C_C7445_EX03.4c.indd 117 6/8/10 2:48 PM6/8/10 2:48 PM

EX 118 Excel | Tutorial 3 Working with Formulas and Functions

Understanding Cell References Most Excel workbooks are created to record and analyze data. To do this effectively, you enter data in cells in a worksheet, and then reference the cells with data in formulas that perform calculations on that data, such as adding the total of a column of numbers as part of a budget.

R E

FE R

E N

C E

Entering Relative, Absolute, and Mixed References • To enter a relative reference, type the cell reference as it appears in the worksheet.

For example, enter B2 for cell B2. • To enter an absolute reference, type $ (a dollar sign) before both the row and column

references. For example, enter $B$2. • To enter a mixed reference, type $ before either the row or column reference. For

example, enter $B2 or B$2. or • Select the cell reference you want to change. • Press the F4 key to cycle the reference from relative to absolute to mixed and then

back to relative.

Diane has already done a lot of the work on her family budget. She used data from the past year to estimate the couple’s monthly expenses for the upcoming year and entered this data into an Excel workbook. You will open this workbook and review her estimates of the family’s monthly expenses.

To open Diane’s workbook: ◗ 1. Open the Drake workbook located in the Excel3\Tutorial folder included with

your Data Files, and then save the workbook as Drake Family Budget in the same folder.

◗ 2. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4.

◗ 3. Switch to the Budget worksheet, and then review its contents.

◗ 4. Scroll down to view the range A18:N32. This range lists Diane’s estimate of her family’s monthly expenses by category for the upcoming year. See Figure 3-1.

C_C7445_EX03.4c.indd 118C_C7445_EX03.4c.indd 118 6/8/10 2:48 PM6/8/10 2:48 PM

EX 119Tutorial 3 Working with Formulas and Functions | Excel

Diane wants you to calculate the total expenses for each month. You’ll start by insert- ing the formula to calculate the January expenses and then paste that formula into the remaining months of the year.

monthly expenses by category

Figure 3-1 Monthly expenses for the Drake family

To calculate the total monthly expenses: ◗ 1. In cell C32, enter the formula =SUM(C22:C31) to add the estimated expenses

for the month of January. The value 6,460 is displayed in cell C32, indicating that Diane estimates the couple will spend $6,460 in January of the upcoming year.

◗ 2. Click cell C32 to select it.

◗ 3. Click the Home tab on the Ribbon, if necessary, and then click the Copy button in the Clipboard group.

◗ 4. Select the range D32:N32, and then click the Paste button in the Clipboard group. The SUM function is pasted into the selected range, calculating the total expenses for the remaining months of the year. See Figure 3-2.

C_C7445_EX03.4c.indd 119C_C7445_EX03.4c.indd 119 6/8/10 2:48 PM6/8/10 2:48 PM

EX 120 Excel | Tutorial 3 Working with Formulas and Functions

Using Relative References When you entered the formula in cell C32 to sum the January expenses, Excel interprets the cell references in that formula relative to the location of cell C32. In other words, Excel interprets the formula =SUM(C22:C31) as adding the values entered in the 10 cells directly above cell C32.

Excel uses this interpretation of the cell references when the formula is pasted into other cells. For example, when you pasted the formula in cell D32, the formula changed to =SUM(D22:D31), which has a different cell reference but the same meaning: adding the values of the 10 cells directly above the active cell. The formulas in the remaining cells of the range D32:E32 were similarly adjusted so that each formula displays the total expenses for the selected month.

The cell references used in these formulas are called relative references because when Excel copies and pastes them, they are always interpreted in relation, or relative, to the location of the cell containing the formula. Figure 3-3 illustrates how a relative cell refer- ence in a formula changes when the formula is copied to another group of cells.

monthly totals

formula to calculate the sum of values in the range D22:D31

Figure 3-2 Total monthly expenses

◗ 5. Review the total expenses for each month. January and August are particularly expensive months because Glenn has to pay tuition and purchase books for the upcoming semester.

◗ 6. Click each cell in the range D32:N32, reviewing the formula entered in the cell. The formulas pasted into the range D32:N32 all calculate the sums of values in different cell references. For example, the formula =SUM(D22:D31) was inserted in cell D32, the formula =SUM(E22:E31) was inserted in cell E32, and so forth.

C_C7445_EX03.4c.indd 120C_C7445_EX03.4c.indd 120 6/8/10 2:48 PM6/8/10 2:48 PM

EX 121Tutorial 3 Working with Formulas and Functions | Excel

In this fi gure, the formula =A2 entered in cell D5 displays the value of 10, which is the value entered in cell A2. When pasted to a new location, each of the pasted formu- las contains a reference to a cell that is three rows up and three rows to the left of the current cell’s location. One of the great advantages of relative references is that you can quickly generate row and column totals without having to worry about revising the for- mulas as you copy them to new locations.

Using Absolute References A good practice when designing a workbook is to enter values in their own cells in one location of the worksheet, and then reference the appropriate cells in formulas through- out the worksheets. This reduces the amount of data entry when you need to use the same data in more than one location. It also makes it faster and more accurate when you need to change a data value, as all the formulas based on that cell are updated to refl ect the new value.

Next, you will enter the Drakes’ monthly income. The couple’s income changes in the summer as Diane works fewer hours and Glenn works more. Rather than inserting these same values each month, you’ll enter them in cells at the top of the worksheet and then reference those values in the income/expenses table at the bottom of the worksheet. Later, if Diane modifi es the monthly income estimates, you’ll need to change them in only one location rather than in 12 different locations.

formula references a cell three rows up and three columns to the left of the active cell

when copied to new cells, each formula still references a cell three rows up and two columns to the left

values returned by each formula

Figure 3-3 Formulas using a relative reference

C_C7445_EX03.4c.indd 121C_C7445_EX03.4c.indd 121 6/8/10 2:48 PM6/8/10 2:48 PM

EX 122 Excel | Tutorial 3 Working with Formulas and Functions

To enter the couple’s estimated monthly income: ◗ 1. Scroll to the top of the worksheet, and then enter the following data in the speci-

fied cells:

cell D5: 2800 cell E5: 2100 cell D6: 1300 cell E6: 2600

◗ 2. Select the range D7:E7. You’ll enter the monthly income totals in these cells.

◗ 3. In the Editing group on the Home tab, click the Sum button . The total esti- mated income is calculated for the school and summer months. See Figure 3-4.

estimated income during the school and summer months

Sum button

Figure 3-4 Monthly income estimates

Next, you’ll calculate the couple’s projected income for January through May by referencing the values you just entered at the top of the worksheet. The couple will have the same income for the next four months as they did in January, so you can copy the formulas from January into February through May.

To insert the monthly income for January through May: ◗ 1. Click cell C19, type =D5, and then press the Enter key. The value 2,800, Diane’s

income for January, appears in cell C19.

◗ 2. In cell C20, enter the formula =D6. The value 1,300, Glenn’s income for January, appears in cell C20.

◗ 3. In cell C21, enter the formula =C19+C20. This formula calculates the total income for the couple in the month of January, displaying the value 4,100.

◗ 4. Select the range C19:C21, and then click the Copy button in the Clipboard group on the Home tab. The three formulas are copied to the Clipboard.

◗ 5. Select the range D19:G21, and then click the Paste button in the Clipboard group. The formulas are pasted and calculate the couple’s income for January through May. See Figure 3-5.

C_C7445_EX03.4c.indd 122C_C7445_EX03.4c.indd 122 6/8/10 2:48 PM6/8/10 2:48 PM

EX 123Tutorial 3 Working with Formulas and Functions | Excel

The formulas you copied and pasted from January resulted in incorrect values for February, March, April, and May. The February column show the couple’s estimated monthly income for the summer, and the March, April, and May columns display the value 0. What went wrong?

The problem is that the formulas use relative references. For example, the formula in cell C19 is =D5, which references the cell located 14 rows up and 1 column to the left of the current cell. When you pasted that formula to cell G19, Excel inserted the formula =H5, which references the empty cell located 14 rows up and 1 column to the left of cell G19, so that the value displayed in the cell is 0.

Instead of using relative references, you need to use a cell reference that will remain fi xed when the formula is copied to a new location. Cell references that remain fi xed are called absolute references. In Excel, absolute references have a $ (dollar sign) before each column and row designation. For example, B8 is a relative reference to cell B8, whereas $B$8 is an absolute reference to cell B8. When you copy a formula that con- tains an absolute reference to a new location, the reference does not change. Figure 3-6 shows an example of how copying a formula with an absolute reference results in the same cell reference being pasted in different cells regardless of their location.

estimated monthly income

incorrect values are displayed

monthly incomes match estimates

Figure 3-5 Income values inserted for January through May

C_C7445_EX03.4c.indd 123C_C7445_EX03.4c.indd 123 6/8/10 2:48 PM6/8/10 2:48 PM

EX 124 Excel | Tutorial 3 Working with Formulas and Functions

In this fi gure, the sales tax of different purchases is calculated and displayed. All items have the same 5 percent tax rate applied to the purchase, with the tax rate stored in cell A2. The sales tax and total cost of the fi rst item are calculated in cells D2 and E2, respectively. When those formulas are copied and pasted to the remaining purchases, the relative references in the formulas are modifi ed to point to the new location of the purchase cost; however, the sales tax rate continues to point to cell A2, regardless of the location of the selected cell.

You will modify the formulas in the Budget worksheet so that they reference Diane’s income estimates using absolute rather than relative references.

formula containing an absolute reference to the sales tax rate in cell A2

when pasted into a new location, the absolute reference remains unchanged

values returned by the cell formulas

$A$2 continues to be referenced in the formula

relative references change based on cell location

absolute reference to cell A2

relative references to cells C2 and D2

Figure 3-6 Formulas using an absolute reference

To use absolute references to display the monthly income: ◗ 1. In cell C19, enter =$D$5. This formula contains an absolute reference to cell D5,

which contains Diane’s monthly income during the school months.

◗ 2. In cell C20, enter =$D$6. This formula contains an absolute reference to cell D6, which contains Glenn’s monthly income during the school months.

◗ 3. Copy the corrected formulas in the range C19:C20, and then paste them in the range D19:G20. As shown in Figure 3-7, the months of February through May now correctly show the monthly income values for the school months.

C_C7445_EX03.4c.indd 124C_C7445_EX03.4c.indd 124 6/8/10 2:48 PM6/8/10 2:48 PM

EX 125Tutorial 3 Working with Formulas and Functions | Excel

Using Mixed References A formula can also include cell references that are mixed. A mixed reference contains both relative and absolute references. For example, a mixed reference for cell A2 can be either $A2 or A$2. In the mixed reference $A2, the column reference to column A is absolute and the reference to row 2 is relative. In the mixed reference A$2, the column reference is relative and the row reference is absolute. As you can see, a mixed reference “locks” one part of the cell reference while the other part can change. When you copy and paste a formula with a mixed reference to a new location, the absolute portion of the cell reference remains fi xed and the relative portion shifts.

Figure 3-8 shows an example of using mixed references to complete a multiplica- tion table. The fi rst cell in the table, cell B3, contains the formula =$A3*B$2, which multiplies the fi rst column entry (A3) by the fi rst row entry (B2), returning the value 1. When this formula is copied to another cell, the absolute portions of the cell references remain unchanged and the relative portions of the references change. For example, if the formula is copied to cell E6, the fi rst mixed cell reference changes to $A6 because the column reference is absolute and the row reference is relative, and the second cell reference changes to E$2 because the row reference is absolute and the column refer- ence is relative. The result is that cell E6 contains the formula =$A6*E$2 and returns the value 16. Other cells in the multiplication table are similarly modifi ed so that each entry returns the multiplication of the row and column headings.

monthly income from January to May

absolute reference to cell D5

Figure 3-7 Results of formulas with absolute references

◗ 4. Click each cell in the range D19:G20 and verify that the formulas =$D$5 and =$D$6 were copied into the appropriate cells.

C_C7445_EX03.4c.indd 125C_C7445_EX03.4c.indd 125 6/8/10 2:48 PM6/8/10 2:48 PM

EX 126 Excel | Tutorial 3 Working with Formulas and Functions

original formula with a mixed cell reference that multiplies the first row by the first column

formula copied to the B3:F7 range with mixed ranges to multiply the first row entries by the first column entries

values returned by each formula

Figure 3-8 A multiplication table using a mixed reference

IN SI

G H

T

Understanding When to Use Relative, Absolute, and Mixed References Part of effective formula writing is knowing when to use relative, absolute, and mixed references. Use relative references when you want to repeat the same formula with cells in different locations on your worksheet. Relative references are commonly used when copying a formula that sums a column of numbers or that calculates the cost of several items by multiplying the item cost by the quantity being purchased.

Use absolute references when you want different formulas to refer to the same cell. This usually occurs when a cell contains a constant value, such as a tax rate, that will be used in formulas throughout the worksheet.

Mixed references are seldom used other than when creating tables of calculated val- ues such as a multiplication table in which the values of the formula or function can be found in the initial rows and columns of the table.

C_C7445_EX03.4c.indd 126C_C7445_EX03.4c.indd 126 6/8/10 2:48 PM6/8/10 2:48 PM

EX 127Tutorial 3 Working with Formulas and Functions | Excel

As you develop formulas and worksheets, you might want to quickly switch a cell reference from relative to absolute or mixed. Rather than retyping the formula, you can switch the reference in editing mode by selecting the cell reference and pressing the F4 key. As you press the function key, Excel cycles through the different reference types, starting by changing a relative reference to an absolute reference, then to a mixed refer- ence with the row absolute, then to a mixed reference with the column absolute, and then fi nally back to a relative reference.

You’ll use the F4 key to cycle through the different types of references as you enter the remaining formulas with the income for the summer months.

To insert the remaining references to the couple’s monthly income: ◗ 1. Click cell H19, type =, and then click cell E5. The formula =E5 appears in the cell,

which remains in Edit mode. This formula enters Diane’s income for the summer months.

◗ 2. Press the F4 key. The formula changes to =$E$5, which is an absolute reference.

Trouble? If the formula shows anything other than the absolute reference, you probably pressed the F4 key too many times. Continue to press the F4 key to loop through all of the cell reference types until the formula returns to =$E$5, which contains the absolute reference.

◗ 3. Press the Enter key. The formula is entered and 2,100 (Diane’s monthly income in the summer) appears in cell H19.

◗ 4. In cell H20, enter the formula =$E$6. This formula uses an absolute reference to enter Glenn’s monthly income in the summer.

◗ 5. In cell H21, enter the formula =H19+H20. This formula adds Diane and Glenn’s income for June; their combined monthly income in the summer is 4,700.

◗ 6. Copy the range H19:H21, and then paste the copied formulas into the range I19:J21. The summer take-home pay values appear for the months of June through August.

You’ll complete the monthly income values for the remaining school months.

◗ 7. Copy the range C19:C21, and then paste it into the range K19:N21. The couple’s monthly income is entered for all months of the year.

Working with Functions Excel functions provide a quick way to calculate summary data such as the total, average, minimum, and maximum values in a collection of values. You’ll use these functions to sum- marize Diane and Glenn’s income and expense data at the top of the Budget worksheet.

Understanding Function Syntax Every function has to follow a set of rules, or syntax, which specifi es how the function should be written. The general syntax of all Excel functions is

FUNCTION(argument1,ƒargument2,ƒ...)

C_C7445_EX03.4c.indd 127C_C7445_EX03.4c.indd 127 6/8/10 2:48 PM6/8/10 2:48 PM

EX 128 Excel | Tutorial 3 Working with Formulas and Functions

where FUNCTION is the name of the function, and argument1, argument2, and so forth are arguments, which are the numbers, text, or cell references used by the function to return a value. Arguments are always separated by a comma.

Not all functions have arguments. Some functions have optional arguments, which are not required for the function to return a value, but can be included to provide more control over how Excel calculates the returned value. If an optional argument is not included, Excel assumes a default value for it. These tutorials show optional arguments within square brackets along with the argument’s default value, as follows:

FUNCTION(argument1,ƒ[argument2=value2,ƒ...])

In this function, argument1 is required, argument2 is an optional argument, and value2 is the default value used for this argument. As you learn more about individual functions, you will learn which arguments are required and which are optional, and the default values used for optional arguments.

The hundreds of available Excel functions are organized into 11 categories. Figure 3-9 describes these different categories.

Optional arguments are always placed last in the argument list.

Category Functions That Cube Retrieve data from multidimensional databases involving online analytical pro-

cessing or OLAP

Database Retrieve and analyze data stored in databases

Date & Time Analyze or create date and time values and time intervals

Engineering Analyze engineering problems

Financial Have financial applications

Information Return information about the format, location, or contents of worksheet cells

Logical Return logical (true-false) values

Lookup & Reference Look up and return data matching a set of specified conditions from a range

Math & Trig Have math and trigonometry applications

Statistical Provide statistical analyses of a set of data

Text Return text values or evaluate text

Figure 3-9 Excel function categories

You can learn about each function using the Help system. Figure 3-10 describes some of the more common Math, Trig, and Statistical functions used in workbooks.

C_C7445_EX03.4c.indd 128C_C7445_EX03.4c.indd 128 6/8/10 2:48 PM6/8/10 2:48 PM

EX 129Tutorial 3 Working with Formulas and Functions | Excel

For example, the AVERAGE function calculates the average value from a collection of numbers. The syntax of the AVERAGE function is

AVERAGE(number1ƒ[,ƒnumber2,ƒnumber3,ƒ...])

where number1, number2, number3, and so forth are either numbers or cell references to numbers. The following formula uses the AVERAGE function to calculate the average of 1, 2, 5, and 8, returning the value 4:

=AVERAGE(1,ƒ2,ƒ5,ƒ8)

However, functions usually reference values entered in the worksheet. So, if the range A1:A4 contains the values 1, 2, 5, and 8, the following formula also returns a value of 4:

=AVERAGE(A1:A4)

Function Category Description AVERAGE(number1 [, number2, number3, ...])

Statistical Calculates the average of a collection of num- bers, where number1, number2, and so forth are either numbers or cell references. Only number1 is required. For more than one cell reference or to enter numbers directly into the function, use the optional arguments number2, number3, and so forth.

COUNT(value1 [, value2, value3, ...])

Statistical Counts how many cells in a range contain numbers, where value1, value2, and so forth are text, num- bers, or cell references. Only value1 is required. For more than one cell reference or to enter numbers directly into the function, use the optional argu- ments value2, value3, and so forth.

COUNTA(value1 [, value2, value3, ...])

Statistical Counts how many cells are not empty in ranges value1, value2, and so forth, or how many numbers are listed within value1, value2, and so forth.

INT(number) Math & Trig Displays the integer portion of a number, number.

MAX(number1 [, number2, number3, ...])

Statistical Calculates the maximum value of a collection of numbers, where number1, number2, and so forth are either numbers or cell references.

MEDIAN(number1 [, number2, number3, ...])

Statistical Calculates the median, or middle, value of a collec- tion of numbers, where number1, number2, and so forth are either numbers or cell references.

MIN(number1 [, number2, number3, ...])

Statistical Calculates the minimum value of a collection of numbers, where number1, number2, and so forth are either numbers or cell references.

RAND() Math & Trig Returns a random number between 0 and 1.

ROUND(number, num_digits) Math & Trig Rounds a number to a specified number of digits, where number is the number you want to round and num_digits specifies how many digits to which you want to round the number.

SUM(number1 [, number2, number3, ...])

Math & Trig Adds a collection of numbers, where number1, number2, and so forth are either numbers or cell references.

Figure 3-10 Common Math, Trig, and Statistical functions

C_C7445_EX03.4c.indd 129C_C7445_EX03.4c.indd 129 6/8/10 2:48 PM6/8/10 2:48 PM

EX 130 Excel | Tutorial 3 Working with Formulas and Functions

PR O

SK IL

LS

Problem Solving: Choosing the Right Summary Function Problem solving involves determining how to best summarize a large sample of data into a few easy-to-use statistics. The field of statistics provides several summary measures, each with its own advantages and disadvantages.

One of the most common statistical approaches is to average the sample data. You can calculate the average in Excel with the AVERAGE function. However, determining an average is not always the best choice. Averages are susceptible to extremely large or small data values. Imagine calculating the average size of homes in a neighborhood that has one huge mansion and several small houses. In this case, the average value is heavily influenced by the size of that single mansion, and might not represent a typical neighbor- hood house.

When the data includes a few extremely large or extremely small values that have the potential to skew results, it’s often better to use the median, or middle, value from the sample. For example, in a survey of nine homes, the median would be the size of the fifth largest—or middle-sized—home in the sample. You can calculate the median in Excel with the MEDIAN function.

Another approach is to calculate the most common value in the data, otherwise known as the mode. The mode is most often used with data that has only a few possible values, each of which might be repeated several times. Rather than using square feet, you might want to express the value in terms of the number of bedrooms each home contains. The mode would return the most common number of bedrooms in the homes included in the sample. You can calculate the mode in Excel using the MODE function.

By knowing which summary measure best fits your data, you can create useful and pre- cise information that will aid you and others in interpreting the results.

The advantage of using cell references is that the values used in the function are vis- ible to users and can be easily edited as needed. Functions can be included as part of larger formulas. For example, the following formula, which includes the MAX function, returns the maximum value from the range A1:A100, and then divides that value by 100:

=MAX(A1:A100)/100

Functions can also be placed inside another function, or nested. If a formula contains several functions, Excel starts with the innermost function and then moves outward. For example, the following formula fi rst calculates the average of the values in the range A1:A100 using the AVERAGE function, and then extracts the integer portion of that value using the INT function:

=INT(AVERAGE(A1:A100))

One challenge of nesting functions is to make sure that you include all of the paren- theses. You can check this by counting the number of left parentheses, and making sure that number matches the number of right parentheses. Excel will also display different levels of nested parentheses in different colors to make it easier to match the opening and closing parentheses in the formula. If the number of parentheses doesn’t match, Excel will not accept the formula and will offer a suggestion for rewriting the formula so the number of left and right parentheses does match.

C_C7445_EX03.4c.indd 130C_C7445_EX03.4c.indd 130 6/8/10 2:48 PM6/8/10 2:48 PM

EX 131Tutorial 3 Working with Formulas and Functions | Excel

To calculate annual income and expenses with the SUM function: ◗ 1. Click cell F9 to select it.

◗ 2. Click the Formulas tab on the Ribbon.

◗ 3. In the Function Library group, click the Math & Trig button. An alphabetical list- ing of all the math and trigonometry functions opens.

◗ 4. Scroll down the list, and then click SUM. The Function Arguments dialog box opens, listing all of the arguments associated with the SUM function.

◗ 5. Click in the worksheet, and then select the range C21:N21. The dialog box reduces to its title bar as you select the range. The range reference, which includes all the monthly income amounts, appears as the value of the Number1 argument. See Figure 3-11.

You can click the Collapse Dialog Box button to shrink the Function Arguments dialog box to see more of the worksheet, select the range, and then click the Expand Dialog Box button to restore the dialog box.

Inserting a Function Functions are organized in the Function Library group on the Formulas tab on the Ribbon. In the Function Library, you can select a function from a function category or you can open the Insert Function dialog box to search for a particular function. When you select a function, the Function Arguments dialog box opens, listing all the arguments associated with that function. Required arguments are in bold type; optional arguments are in normal type.

You’ll use the SUM function to add the total income and expenses for the year in Diane’s proposed budget.

required arguments appear in bold

optional arguments appear in regular type

description of the function

preview of the value displayed in the active cell

preview of the value returned by the function

cell values in the selected range

description of the selected argument

Figure 3-11 Function Arguments dialog box

◗ 6. Click the OK button. The formula =SUM(C21:N21) is inserted into cell F9, which displays the value 51,000. This represents Diane and Glenn’s total annual income.

◗ 7. Click cell F13. This is where you want the SUM function to add the monthly expenses for the year.

◗ 8. In the Function Library group on the Formulas tab, click the Math & Trig button, and then click SUM. The Function Arguments dialog box opens. You’ll enter the monthly expenses stored in the range C32:N32 for the argument.

C_C7445_EX03.4c.indd 131C_C7445_EX03.4c.indd 131 6/8/10 2:48 PM6/8/10 2:48 PM

EX 132 Excel | Tutorial 3 Working with Formulas and Functions

To calculate the couple’s average monthly income: ◗ 1. Click cell F10. This is the cell in which you want to enter the AVERAGE function

formula.

◗ 2. In the Function Library group on the Formulas tab, click the Insert Function but- ton. The Insert Function dialog box opens.

◗ 3. In the Search for a function box, type Calculate an average value, and then click the Go button. Functions for calculating an average appear in the Select a func- tion box. See Figure 3-13.

You can also click the Insert Function button on the formula bar to open the Insert Function dialog box.

values added in the SUM function formula in cell F9

SUM function formula returns the total annual expenses

total annual expenses

total annual income

Figure 3-12 Total annual income and expenses

Diane projects that she and Glenn will earn roughly $6,000 more than they will spend throughout the year. It’s easier for Diane to plan her budget if she knows how much, on average, the couple earns and spends each month. You can use the AVERAGE function to do this calculation using the same method you used for the SUM function.

If you aren’t sure of the function’s name or category, you can use the Insert Function dialog box. The Insert Function dialog box organizes all of the functions by category and includes a search feature for locating functions that perform particular calculations.

◗ 9. Click in the worksheet, and then select the range C32:N32.

◗ 10. In the Function Arguments dialog box, click the OK button. The formula =SUM(C32:N32) is inserted in cell F13, which displays the value 45,150. This rep- resents the total projected expenses for the upcoming year. See Figure 3-12.

C_C7445_EX03.4c.indd 132C_C7445_EX03.4c.indd 132 6/8/10 2:48 PM6/8/10 2:48 PM

EX 133Tutorial 3 Working with Formulas and Functions | Excel

description of the function

functions that match the description (your list might differ)

syntax and description of the selected function

click to access online help about the selected function

Figure 3-13 Insert Function dialog box

◗ 4. Verify that AVERAGE is selected in the Select a function box, and then click the OK button. The Function Arguments dialog box opens with the arguments for the AVERAGE function. A range reference for a cell directly above this cell already appears for the Number1 argument.

◗ 5. Select the range reference in the Number1 argument box, and then select the range C21:N21 in the worksheet.

◗ 6. Click the OK button. The dialog box closes, and the formula =AVERAGE(C21:N21) is entered in cell F10, which displays the value 4,250—the couple’s average monthly income.

How does the couple’s average monthly income compare to their average monthly expenses? To fi nd out, you’ll use the AVERAGE function again. Because the function has already been used in your workbook, you can select it from a list of recently used functions.

To calculate the average monthly expenses: ◗ 1. Click cell F14. This is the cell where you want to calculate the average monthly

expenses.

◗ 2. On the formula bar, click the Insert Function button . The Insert Function dia- log box opens.

◗ 3. If necessary, click the Or select a category arrow, and then click Most Recently Used. The most recently used functions, sorted in order of recent use, appear in the Select a function box. The AVERAGE function is at the top followed by the SUM function.

◗ 4. Verify that AVERAGE is selected in the Select a function box, and then click the OK button.

◗ 5. Select the range C32:N32 in the worksheet to insert the range reference C32:N32 in the Number1 box.

C_C7445_EX03.4c.indd 133C_C7445_EX03.4c.indd 133 6/8/10 2:48 PM6/8/10 2:48 PM

EX 134 Excel | Tutorial 3 Working with Formulas and Functions

◗ 6. Click the OK button. The formula =AVERAGE(C32:N32) is entered in cell F14, dis- playing the value 3,763. This represents the average expenses per month under Diane’s budget. See Figure 3-14.

AVERAGE function formula returns the average of the values in the range C32:N32

average monthly income

average monthly expenses

Figure 3-14 Average family income and expenses

The average monthly expense, 3,763, is displayed to the nearest dollar amount. This is because Diane has formatted the workbook to not display decimal values. The actual value, 3,762.50, is stored in the cell although it is not displayed.

Typing a Function After you become more familiar with functions, it is often faster to type the functions directly in cells rather than using the Insert Function dialog box or the Function Library. As you begin to type a function name within a formula, a list of functions that begin with the letters you typed appears. For example, when you type S, the list shows all of the functions starting with the letter S; when you type SU, the list shows only those func- tions starting with the letters SU, and so forth. This helps to ensure that you’re entering a legitimate Excel function name.

The income and expenses averages show that the couple will bring in about $500 more than they spend each month. That does not leave much money to deal with an unexpected expense, so Diane wants to know how much variation is in the budget. What is the most money she could expect to take home during a single month in the upcoming year? What is the least? And what are the highest and lowest values for the monthly expenses? You’ll use the MAX and MIN functions to calculate those values.

To calculate the minimum value for monthly income and expenses: ◗ 1. Click cell F11. This is the cell in which you want to enter the monthly income.

◗ 2. Type =M. As you type a formula, a list with function names starting with M opens.

◗ 3. Type I. The list shows only those functions starting with MI. See Figure 3-15. As soon as the function you want appears in the list, you can double-click its name to enter it in the cell without typing the rest of its name.

C_C7445_EX03.4c.indd 134C_C7445_EX03.4c.indd 134 6/8/10 2:48 PM6/8/10 2:48 PM

EX 135Tutorial 3 Working with Formulas and Functions | Excel

list of Excel functions starting with MI

ScreenTip describes the selected function

Figure 3-15 Function being typed into a cell

◗ 4. Double-click MIN in the list box. The MIN function with its opening parenthesis is inserted into cell F11 and a ScreenTip shows the syntax for the function. At this point, you can either type in the range reference or select the range with your mouse. To avoid typing errors, it’s often better to use your mouse to enter range references.

◗ 5. Select the range C21:N21. The range reference is added to the formula.

◗ 6. Type ) (the closing parenthesis), and then press the Enter key. The formula =MIN(C21:N21) is inserted in cell F11, displaying the value 4,100. This is the mini- mum amount that Diane expects the couple to bring home in a single month for the upcoming year.

Next, you’ll calculate the minimum monthly expense projected for the year.

◗ 7. Click cell F15, and then repeat Steps 2 through 6 to enter the formula =MIN(C32:N32) in cell F15. The cell displays the value 2,795, which is the lowest amount that Diane expects to spend in a single month in the upcoming year.

Be sure to end this and all functions with the closing parenthesis ) to ensure that Excel interprets the formula correctly.

The fi nal piece of the year-end summary is the maximum monthly value for both income and expenses. Maximum values are calculated using the MAX function.

To calculate the maximum value for monthly income and expenses: ◗ 1. Click cell F12, and then enter the formula =MAX(C21:N21). The value 4,700

appears in cell F12, indicating that the maximum income the couple can expect in a single month is $4,700.

Trouble? If #NAME? appears in the cell, you probably mistyped the function name. Edit the formula to correct the misspelling.

◗ 2. Click cell F16, and then enter the formula =MAX(C32:N32). The value 6,985 appears in cell F16, indicating that the maximum expenses for a single month are projected to be $6,985. See Figure 3-16.

C_C7445_EX03.4c.indd 135C_C7445_EX03.4c.indd 135 6/8/10 2:48 PM6/8/10 2:48 PM

EX 136 Excel | Tutorial 3 Working with Formulas and Functions

Based on the year-end summary, Diane and Glenn’s monthly income will range from a minimum of $4,100 to a maximum of $4,700 with an average of $4,250. Monthly expenses, on the other hand, range from a minimum of $2,795 to a maximum of $6,985 with an average of $3,763. This budget does not have a lot of wiggle room.

Diane has just been promoted at work. Her income will increase to $3,100 per month during the school year. She wants to know how this affects the year-end summary.

MAX function formula returns the maximum value in the range C32:N32

minimum and maximum monthly income

minimum and maximum monthly expenses

Figure 3-16 Year-end summary values

To modify Diane’s estimated income: ◗ 1. In cell D5, enter the value 3100.

◗ 2. Confirm that Diane’s monthly income for January through May and September through December has been automatically updated.

◗ 3. Review how the year-end summary data has changed. See Figure 3-17.

income projection changed from 2,800 to 3,100

Diane's income during school months updated to $3,100

total income updated to reflect the new income projection

summary estimates updated based on new income projection

Figure 3-17 Revised income projection

C_C7445_EX03.4c.indd 136C_C7445_EX03.4c.indd 136 6/8/10 2:48 PM6/8/10 2:48 PM

EX 137Tutorial 3 Working with Formulas and Functions | Excel

With Diane’s promotion, the couple’s annual income increases from $51,000 to $53,700 and the monthly average increases from $4,250 to $4,475. The couple’s income should exceed their expenses by about $700 per month. The monthly income now ranges from a minimum of $4,400 up to a maximum of $4,700.

Diane now has a better picture of the family’s fi nances for the upcoming year, and she is more confi dent about how to manage the couple’s budget. She and Glenn hope to save enough for a down payment on a house in a few years. With the promotion, this seems like a real possibility. In the next session, you’ll help Diane explore the couple’s options in planning for the purchase of a house.

R E

V IE

W

Session 3.1 Quick Check

1. You need to reference cell Q57 in a formula. What is the absolute cell refer- ence? What are the two mixed references?

2. If cell R10 contains the formula =R1+R2. What formula is entered if this formula is copied and pasted into cell S20?

3. If cell T10 contains the formula =$T1+T$2. What formula is entered if this for- mula is copied and pasted into cell U20?

4. If cell V10 contains the formula =AVERAGE($U1:$U5). What formula is entered if this formula is copied and pasted into cell W20?

5. What are optional arguments? What happens if you do not include an optional argument in a function?

6. What function formula can you use to add the numbers in the range X1:X10? 7. The range of a set of values is defi ned as the maximum value minus the mini-

mum value. What formula with functions can you enter to calculate the range of the values in Y1:Y10?

8. What formula with functions can you enter to calculate the ratio of the maxi- mum value in the range Z1:Z10 to the minimum value?

C_C7445_EX03.4c.indd 137C_C7445_EX03.4c.indd 137 6/8/10 2:48 PM6/8/10 2:48 PM

EX 138 Excel | Tutorial 3 Working with Formulas and Functions

The TODAY function is a date function that displays the current date.

A financial function is a function related to monetary calculations, such as loans and payments.

A date function is a function that inserts or calculates dates and times.

A logical function is a function that works with statements that are either true or false.

An IF function is a logical function that tests a condition and then returns one value if the condition is true and another value if the condition is false.

This IF function tests whether the value of cell E33 is greater than or equal to the value in cell L11 (E33>=$L$11). If the condition is true, the function returns the first value ($L$10); if false, it returns the second value (0).

The PMT function calculates the amount of a monthly loan payment, based on rate (the interest rate per month), nper (the total number of months to pay back the loan), and pv (the present value of the loan).

SESSION 3.2 VISUAL OVERVIEW

C_C7445_EX03.4c.indd 138C_C7445_EX03.4c.indd 138 6/8/10 2:48 PM6/8/10 2:48 PM

EX 139Tutorial 3 Working with Formulas and Functions | Excel

Payments are expressed as negative numbers because they are treated as expenses.

The PMT function is a financial function that calculates the monthly payment required to pay back a loan.

The Auto Fill Options button appears after you complete the fill so you can select whether to copy cell content and formatting, extend the data series, fill only the cell formatting, or fill only cell content.

The fill handle appears in the lower-right corner of a selected cell or range. Dragging the fill handle over an adjacent cell or range copies the content and formatting from the original cells into the selected range.

AutoFill can extend a series of numbers, patterned text, and dates into the new selection. In this case, AutoFill generated the abbreviations of month names.

AutoFill copies content and formats from a cell or range into an adjacent cell or range. The cell contents can be text, values, or formulas.

AUTOFILL AND MORE FUNCTIONS

C_C7445_EX03.4c.indd 139C_C7445_EX03.4c.indd 139 6/8/10 2:48 PM6/8/10 2:48 PM

EX 140 Excel | Tutorial 3 Working with Formulas and Functions

Entering Data and Formulas with AutoFill Diane and Glenn hope to purchase a home in the next three years. Currently, they have $4,000 in their main savings account, and they plan to open a second account reserved for saving toward a down payment on a home. You will enter the initial balance for the main account and set up the home account with an initial balance of $0 into the Budget worksheet.

To enter the savings account information: ◗ 1. If you took a break after the previous session, make sure the Drake Family Budget

workbook is open and the Budget worksheet is active.

◗ 2. In cell K5, enter 4,000 (the starting amount in the main savings account).

◗ 3. In cell K6, enter 0 (the starting amount in the home savings account).

◗ 4. In cell K7, enter the formula =K5+K6 to sum the total initial amount in both accounts. See Figure 3-18.

savings will be divided into a Main account and a Home account

initial savings

Figure 3-18 Savings account information

Diane wants to learn how much the couple could add to their savings accounts each month. To fi nd out, you must fi rst determine the couple’s monthly net cash fl ow, which is equal to the amount of money they earn each month after paying all of their expenses. You will start by formatting the cells where you’ll enter this data and calculating the net cash fl ow during the month of January.

To calculate the net cash flow for January: ◗ 1. Merge and center the range A33:B33, and then right-align the cell contents.

◗ 2. In the merged cell A33, enter Net Cash Flow.

◗ 3. In cell C33, enter the formula =C21–C32. This formula subtracts total expenses from total income for the month of January. The resulting −2,060 indicates a pro- jected shortfall of $2,060 for the month of January.

◗ 4. Apply conditional formatting to cell C33 to highlight the cell with a red fill and red text if the value in the cell is less than 0. Months with negative cash flow will be highlighted on the worksheet. See Figure 3-19.

C_C7445_EX03.4c.indd 140C_C7445_EX03.4c.indd 140 6/8/10 2:48 PM6/8/10 2:48 PM

EX 141Tutorial 3 Working with Formulas and Functions | Excel

Your calculations show that the couple’s expenses will exceed their income by more than $2,000 in January. This is due to the cost of tuition that must be paid that month. You could copy and paste the formula and formatting from cell C33 into the rest of the row to calculate the net cash fl ow for the other months, as you’ve done before, but AutoFill is faster.

Using the Fill Handle After you select a range, the fi ll handle appears in the lower-right corner of the selection. When you drag the fi ll handle over an adjacent range, Excel uses AutoFill to copy the content and formats from the original cell into the adjacent range. This process is often more effi cient than the two-step process of copying and pasting.

conditional formatting highlights the negative cash flow (less than 0) for January

January net cash flow equals income (cell C21) minus expenses (cell C32)

Figure 3-19 January net cash fl ow

R E

FE R

E N

C E

Copying Formulas and Formats with AutoFill • Select the cell or range that contains the formula or formulas you want to copy. • Drag the fill handle in the direction you want to copy the formula(s) and then release

the mouse button. • To copy only the formats or only the formulas, click the Auto Fill Options button and

select the appropriate option. or • Select the cell or range that contains the formula or formulas you want to copy. • In the Editing group on the Home tab, click the Fill button. • Select the appropriate fill direction and fill type. or

Click Series, enter the desired fill series options, and then click the OK button.

C_C7445_EX03.4c.indd 141C_C7445_EX03.4c.indd 141 6/8/10 2:48 PM6/8/10 2:48 PM

EX 142 Excel | Tutorial 3 Working with Formulas and Functions

You will use the fi ll handle to copy the formula and conditional formatting you entered in cell C33 into the remaining cells in the Net Cash Flow row.

To copy the cash flow formula and formatting with the fill handle: ◗ 1. Click cell C33 to select it, if necessary. The fill handle appears in the lower-right

corner of the cell.

◗ 2. Position the pointer over the fill handle in the lower-right corner of the cell. The pointer changes to .

◗ 3. Click and drag the fill handle over the range D33:N33. A dotted outline appears around the selected range as you move the pointer.

◗ 4. Release the mouse button. The selected range is filled in with the formula and for- matting from cell C33, and the Auto Fill Options button appears in the lower-right corner of the selected cells. See Figure 3-20.

With AutoFill, formulas can easily be copied into the wrong range; if that happens, click the Undo button and try again.

the formulas and formats in cell C33 are filled into the selected range

conditional formatting highlights months of negative cash flow

fill handle

Auto Fill Options button

formula to calculate the January net cash flow

Figure 3-20 Formulas and formatting pasted with AutoFill

◗ 5. Review the monthly net cash flows to confirm that AutoFill correctly copied the formula and conditional formatting into the selected range.

These calculations provide Diane with a better picture of how the couple’s net cash fl ow varies from month to month. Only in January and August, when Glenn’s tuition payments are due, do the couple’s expenses exceed their income. In most months, their income exceeds expenses by about $1,500. In June, however, the net cash fl ow, while positive, is projected to be only $205.

C_C7445_EX03.4c.indd 142C_C7445_EX03.4c.indd 142 6/8/10 2:48 PM6/8/10 2:48 PM

EX 143Tutorial 3 Working with Formulas and Functions | Excel

Using the Auto Fill Options Button By default, AutoFill copies both the content and the formatting of the original range to the selected range. However, sometimes you might want to copy only the content or only the formatting. The Auto Fill Options button that appears after you release the mouse button lets you specify what is copied. As shown in Figure 3-21, clicking this button provides a list of Auto-Fill options. The Copy Cells option, which is the default, copies both the content and the formatting. The Fill Formatting Only option copies the formatting into the selected cells but not any content. The Fill Without Formatting option copies the content but not the formatting.

copies the formulas, values, and formatting (the default)

copies only the formatting

copies only the formulas or values but not the formatting

Figure 3-21 Auto Fill Options button

Filling a Series AutoFill can also be used to create a series of numbers, dates, or text based on a pattern. To create a series of numbers, you enter the initial values in the series in a selected range and then use AutoFill to complete the series. Figure 3-22 shows how AutoFill can be used to insert the numbers from 1 to 10 in a selected range. You enter the fi rst few num- bers in the range A1:A3 to establish the pattern for AutoFill to use, consecutive positive integers in this example. Then, you select the range and drag the fi ll handle over the cells where you want the pattern continued. In Figure 3-22, the fi ll handle is dragged over the range A4:A10 and Excel fi lls in the rest of the series.

fill handle

initial range establishes the series pattern

dragging the fill handle extends the pattern to the larger range

Figure 3-22 AutoFill extends a numeric sequence

AutoFill can extend a wide variety of series, including dates and times and patterned text. Figure 3-23 shows examples of some series that AutoFill can generate. In each case, you must provide enough information for AutoFill to identify the pattern. AutoFill can recognize some patterns from only a single value, such as Jan or January, to create a series of month abbreviations or names, or Mon or Monday, to create a series of the days of the week. A text pattern that includes a text string and a number such as Region 1, Region 2, and so on can also be automatically extended using AutoFill.

C_C7445_EX03.4c.indd 143C_C7445_EX03.4c.indd 143 6/8/10 2:48 PM6/8/10 2:48 PM

EX 144 Excel | Tutorial 3 Working with Formulas and Functions

For more complex patterns, you can use the Series dialog box. To do so, enter the fi rst value of the series in a worksheet cell, select the entire range that will contain the series, click the Fill button in the Editing group on the Home tab, and then click Series. The Series dialog box opens. From the Series dialog box you can specify a linear or growth series for numeric values; a Date series for dates that increase by day, weekday, month, or year; or an AutoFill series for patterned text. With numeric values, you can also specify the step value (indicating how much each numeric value increases over the previous entry) and a stop value (to specify the endpoint for the entire series).

Type Initial Pattern Extended Series Values 1, 2, 3 4, 5, 6, ...

2, 4, 6 8, 10, 12, ...

Dates and Times Jan Feb, Mar, Apr, ...

January February, March, April, ...

15-Jan, 15-Feb 15-Mar, 15-Apr, 15-May, ...

12/30/2013 12/31/2013, 1/1/2014, 1/2/2014, ...

12/31/2013, 1/31/2014 2/28/2014, 3/31/2014, 4/30/2014, ...

Mon Tue, Wed, Thu, ...

Monday Tuesday, Wednesday, Thursday, ...

11:00AM 12:00PM, 1:00PM, 2:00PM, ...

Patterned Text 1st period 2nd period, 3rd period, 4th period, ...

Region 1 Region 2, Region 3, Region 4, ...

Quarter 3 Quarter 4, Quarter 1, Quarter 2, ...

Qtr3 Qtr4, Qtr1, Qtr2, ...

Figure 3-23 AutoFill applied to values, dates and times, and patterned text

R E

FE R

E N

C E

Creating a Series with AutoFill • Enter the first few values of the series into a range. • Select the range, and then drag the fill handle of the selected range over the cells you

want to fill. or • Enter the first few values of the series into a range. • Select the entire range into which you want to extend the series. • In the Editing group on the Home tab, click the Fill button, and then click Down, Right,

Up, Left, Series, or Justify to set the direction in which you want to extend the series.

Diane wants to see how the monthly balances in the main savings account are affected by the couple’s changing income and expenses. She wants to make sure that the balance doesn’t drop too low after months with particularly high expenses—such as January and August. You’ll add data to the worksheet to display the monthly savings bal- ance, starting with the month labels.

C_C7445_EX03.4c.indd 144C_C7445_EX03.4c.indd 144 6/8/10 2:48 PM6/8/10 2:48 PM

EX 145Tutorial 3 Working with Formulas and Functions | Excel

To use AutoFill to enter a series of months: ◗ 1. In cell C35, enter Jan. This is the first value in the series. Because “Jan” is a com-

mon abbreviation for January, Excel recognizes it as a month and you don’t need to type “Feb” for the next month in the series.

◗ 2. Select cell C35, if necessary.

◗ 3. Drag the fill handle over the range D35:N35. As you drag the fill handle, ScreenTips show the month abbreviations for the selected cell.

◗ 4. Release the mouse button. AutoFill enters the remaining three-letter abbreviations for each month of the year.

◗ 5. Select the range C18:N18, and then click the Format Painter button in the Clipboard group on the Home tab. You’ll apply this formatting to the entries in the range C35:N35.

◗ 6. Click cell C35 to apply the format to the range C35:N35, and then click cell A35 to deselect the column titles. See Figure 3-24.

month abbreviations inserted with AutoFill

initial entry

Figure 3-24 Month labels and formatting

Next, you’ll enter formulas to calculate the changing balance in the couple’s main savings account. The main savings account balance at the end of each month is deter- mined by four factors:

1. The balance at the beginning of the month 2. Money deposited from the couple’s paychecks at the beginning of the month 3. Money withdrawn to pay for expenses 4. Money transferred into a home savings account reserved for a down payment on a

mortgage. (Diane and Glenn have not yet decided how much they want to transfer into this account, so you’ll leave that value blank for now.)

You’ll start by calculating the balance in the couple’s account at the end of January.

To calculate the January balance in the savings account: ◗ 1. In cell C36, enter the formula =K5. The formula references the balance in the

main savings account at the beginning of the year, which is stored in cell K5.

◗ 2. In cell C37, enter =C21 to retrieve the couple’s January income. The relative reference will change when you copy the formula to other months.

C_C7445_EX03.4c.indd 145C_C7445_EX03.4c.indd 145 6/8/10 2:48 PM6/8/10 2:48 PM

EX 146 Excel | Tutorial 3 Working with Formulas and Functions

◗ 3. In cell C38, enter =C32 to retrieve the January expenses. The relative reference will change when you copy the formula to other months. You’ll leave cell C39 blank because, at this point, you won’t assume that any money will be transferred from the main savings account to the home savings account.

◗ 4. In cell C40, enter =C36+C37−C38−C39. This formula calculates the ending bal- ance for the main savings account, which is equal to the starting balance plus any deposits minus the withdrawals and transfers. Cell C40 displays 1,940, which is the projected balance in the main savings account at the end of January.

◗ 5. Format the range C36:C39 to add borders around all of the cells.

◗ 6. Use the Format Painter to copy the formatting from cell C32 to cell C40, and then change the fill color to Blue, Accent 1, Lighter 60% (the fifth color in the third row of the Theme Colors section) to change the background color of the cell.

◗ 7. Click cell A35. Figure 3-25 shows the formatted January savings values.

balance at the start of January

balance at the end of January

transfers to the down payment savings account (none)

deposits from January income

withdrawals to cover January expenses

Figure 3-25 January savings

At this point, the couple’s projected savings at the end of January will be $1,940, which is $2,060 less than their starting balance of $4,000 at the beginning of the year. The savings formulas for the remaining months are the same as for January except that their starting balances are based on the ending balance of the previous month. You will calculate the activity in the couple’s main savings account for the remaining months of the year.

To calculate the remaining balances in the main savings account: ◗ 1. Select the range C36:C40 and drag the fill handle over the February savings in

the range D36:D40.

◗ 2. Change the formula in cell D36 to =C40 so that the February starting balance for the main savings account is based on the January ending balance.

◗ 3. Select the range D36:D40, and then drag the fill handle over the range E36:N40. The formulas and formatting from February are copied into the remaining months of the year.

C_C7445_EX03.4c.indd 146C_C7445_EX03.4c.indd 146 6/8/10 2:48 PM6/8/10 2:48 PM

EX 147Tutorial 3 Working with Formulas and Functions | Excel

Developing a Savings Plan Under her current budget projections, Diane expects to have $12,550 in the main sav- ings account at the end of the next year but nothing in the home savings account. She wants to transfer money into the home savings account each month. Because the home savings account is used for longer-term savings, Diane cannot withdraw money from it without penalty. So, she wants to make sure the main savings account always has enough money to meet monthly expenses and any unexpected bills without relying on money from the home savings account.

Diane needs to balance her desire to keep a reasonable amount in the main savings account and her desire to save enough for a down payment on a home mortgage. To achieve this balance, she needs to determine her overall savings goal and how soon she and Glenn want to meet that goal.

Diane wants to know how much money the couple can save if they transfer $500 to $1,000 into the home savings account each month for the next three years. You’ll create a table that shows the total amount saved in one, two, and three years from deposits starting at $500 that increase in $100 increments through $1,000.

◗ 4. Click cell A35 to deselect the main savings account data. See Figure 3-26.

savings balance at the end of each month

Figure 3-26 January through December savings

◗ 5. In cell L5, enter the formula =N40. The ending balance of the main savings account in December—12,550—appears in cell L5. Diane can quickly see that the couple’s savings in the main account will increase by $8,550 in the upcoming year.

To create a table for different savings plans: ◗ 1. Go to the Home Savings Plan worksheet.

◗ 2. Merge and center the range B3:G3, enter Savings Deposit per Month in the merged cell, and then format the merged cell using the Heading 2 cell style.

◗ 3. In cell A4, enter Months, and then format the cell in bold.

◗ 4. In cell B4, enter 500 and then, in cell C4, enter 600. You entered the first two values in the series so that you could extend the numeric series.

C_C7445_EX03.4c.indd 147C_C7445_EX03.4c.indd 147 6/8/10 2:48 PM6/8/10 2:48 PM

EX 148 Excel | Tutorial 3 Working with Formulas and Functions

Next, you’ll enter formulas to calculate the amount of money saved under each plan. The amount saved is equal to the number of months of savings multiplied by the deposit per month. You’ll create this table using the same formulas with mixed cell references discussed earlier in Figure 3-8.

◗ 5. Select the range B4:C4, and then drag the fill handle to cell G4. The values entered in the series—500, 600, 700, 800, 900, and 1000—are the different amounts the Drakes might transfer into their home savings account each month.

Trouble? If the number 600 was entered in each cell instead of the series, you probably did not select both cells B4 and C4 before dragging the fill handle. Repeat Step 5, being sure to select both cells before filling.

◗ 6. In the range A5:A7, enter the values 12, 24, and 36. These monthly values are equal to one year, two years, and three years, respectively. You entered the years in months because Diane and Glenn plan to deposit money into their home savings account each month. So, they would make 12 deposits in one year, they would make 24 deposits in two years, and they would make 36 deposits in three years.

◗ 7. Format the nonadjacent range B4:G4;A5:A7 with the Input cell style.

To enter formulas with mixed references to calculate the savings amounts:

◗ 1. In cell B5, enter =$A5*B$4. This formula uses mixed references to calculate the amount of savings generated by saving $500 per month for 12 months. The calcu- lated value 6000 is displayed in the cell.

◗ 2. Select cell B5 and drag the fill handle over the range C5:G5 to calculate total sav- ings over 12 months for deposits of $500 to $1,000 per month.

◗ 3. With B5:G5 still selected, drag the fill handle down to G7 to apply the formula to the remaining cells in the multiplication table.

◗ 4. Format the values in the range B5:G7 using a thousands separator with no digits to the right of the decimal point, and add a border around each of the cells in the range.

◗ 5. Click cell B5. Figure 3-27 shows the completed and formatted values.

total amount saved over 12, 24, and 36 months

formula uses mixed cell references to multiply each column value by each row value

Figure 3-27 Savings plan table

C_C7445_EX03.4c.indd 148C_C7445_EX03.4c.indd 148 6/8/10 2:48 PM6/8/10 2:48 PM

EX 149Tutorial 3 Working with Formulas and Functions | Excel

The data shows how increasing the monthly amount that Diane and Glenn save toward a down payment quickly adds up. For example, if they save $800 per month, at the end of three years (36 months), they would have saved $28,800. This is just a little less than the $30,000 they want to save for the down payment. You will add the transfer of $800 from the main savings account to the home savings account each month to the proposed budget. So that Diane can explore the impact of transferring different amounts of money per month, you’ll enter the $800 value at the top of the Budget worksheet where it can be easily referenced and modifi ed.

To specify the $800 transfer amount: ◗ 1. Return to the Budget worksheet.

◗ 2. In cell L10, enter 800. See Figure 3-28.

amount to transfer from the main account to the home account each month

Figure 3-28 Home savings plan

Next, you’ll apply this monthly savings goal to Diane’s proposed budget. You’ll start by calculating the impact of transferring $800 on the January balances in the two savings accounts.

To modify the January balances: ◗ 1. In cell C39, enter the formula =$L$10 to insert the amount of money withdrawn

from the main savings account in January. The ending balance for the main account falls to $1,140. You used an absolute cell reference so that this formula continues to refer to cell L10 when you copy it into the remaining months of the year.

◗ 2. In cell C41, enter the formula =K6 to insert the starting balance ($0) in the home account.

◗ 3. In cell C42, enter the formula =C39 to insert the amount deposited from the main account into the home account.

◗ 4. In cell C43, enter the formula =C41+C42 to calculate the ending balance in the home account.

◗ 5. Use the Format Painter to copy the formatting from the range C38:C40 to the range C41:C43.

C_C7445_EX03.4c.indd 149C_C7445_EX03.4c.indd 149 6/8/10 2:48 PM6/8/10 2:48 PM

EX 150 Excel | Tutorial 3 Working with Formulas and Functions

Now you’ll insert the savings balances for the remaining months of the year, transfer- ring $800 each month from the main savings account to the home savings account. As you did for the main savings account, you need to modify the formulas in row 41 so that the starting balance for February through December is taken from the ending balance of the previous month.

◗ 6. Change the fill color of cell C43 to Orange, Accent 6, Lighter 60% (the last color in the third row of the Theme Colors section).

◗ 7. Click cell C43 to select it, if necessary. See Figure 3-29.

home savings account balance for January

transfer amount specified in cell L10

Figure 3-29 Ending January balances in the Main and Home accounts

To complete the formulas for the home savings account: ◗ 1. Click cell C39, and then drag the fill handle over the range D39:N39. The formula

=$L$10 is inserted in each cell in the range. The ending balance for December drops to $2,950, which is the final balance in the main account after transferring $800 per month.

◗ 2. Copy the range C41:C43, and then paste it into the range D41:D43. The formulas and formatting from January are entered for February. The starting balance for February shows the initial balance in the home savings account, $0, rather than the ending balance from January, $800.

◗ 3. Change the formula in cell D41 to =C43 so that the February starting balance is taken from the January ending balance. Cell D41 correctly shows the February starting balance of 800, and cell D43 displays the value 1,600, the ending balance for February.

◗ 4. Select the range D41:D43, and then drag the fill handle over the range E41:N43. The formulas and formatting from February are copied to the remaining months of the year.

◗ 5. Click cell N43 to deselect the range. Figure 3-30 shows the monthly balances for both accounts from January through December.

C_C7445_EX03.4c.indd 150C_C7445_EX03.4c.indd 150 6/8/10 2:48 PM6/8/10 2:48 PM

EX 151Tutorial 3 Working with Formulas and Functions | Excel

Working with Logical Functions Although Diane is pleased that $9,600 will be moved into the home savings account in the next year, she’s concerned about the amount of money left in the main savings account. Even more troubling are the month-to-month balances in that account. For example, the balance in the main savings account will be only $375 at the end of August and will remain well below $2,000 for several months of the year. Diane is concerned that this savings plan will leave the couple with insuffi cient funds in the main savings account to handle unforeseen expenses.

Part of the problem is that the couple’s net cash fl ow is negative during several months of the year. If they continue to transfer $800 into the home savings account during those months, the main savings account might fall below an acceptable level. Diane wants to modify her savings plan so that money is not transferred into the home savings account unless the net cash fl ow for that month is greater than or equal to $1,000. You need a for- mula that can “choose” whether to transfer the funds. You can build this kind of decision- making capability into a formula through the use of a logical function.