Excel2010 ebook

Chapter 14: Creating Formulas That Look Up Values Tip If the lookup_value argument is text and the range_lookup argument is False, the lookup_value can include wildcard characters * and ?. A very common use for a lookup formula involves an income tax rate schedule (see Figure 14.2). The tax rate schedule shows the income tax rates for various income levels. The following formula (in cell B3) returns the tax rate for the income in cell B2: =VLOOKUP(B2,D2:F7,3) FIGURE 14.2 Using VLOOKUP to look up a tax rate. The lookup table resides in a range that consists of three columns (D2:F7). Because the last argu- ment for the VLOOKUP function is 3, the formula returns the corresponding value in the third col- umn of the lookup table. Note that an exact match is not required. If an exact match is not found in the first column of the lookup table, the VLOOKUP function uses the next largest value that is less than the lookup value. In other words, the function uses the row in which the value you want to look up is greater than or equal to the row value but less than the value in the next row. In the case of a tax table, this is exactly what you want to happen. The HLOOKUP function The HLOOKUP function works just like the VLOOKUP function except that the lookup table is arranged horizontally instead of vertically. The HLOOKUP function looks up the value in the first row of the lookup table and returns the corresponding value in a specified table row. 313

Part II: Working with Formulas and Functions The syntax for the HLOOKUP function is HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) The HLOOKUP function’s arguments are as follows l lookup_value: The value to be looked up in the first row of the lookup table. l table_array: The range that contains the lookup table. l row_index_num: The row number within the table from which the matching value is returned. l range_lookup: Optional. If TRUE or omitted, an approximate match is returned. (If an exact match is not found, the next largest value less than lookup_value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP can’t find an exact match, the function returns #N/A. Tip If the lookup_value argument is text and the range_lookup argument is False, the lookup_value can include wildcard characters * and?. Figure 14.3 shows the tax rate example with a horizontal lookup table (in the range E1:J3). The formula in cell B3 is =HLOOKUP(B2,E1:J3,3) FIGURE 14.3 Using HLOOKUP to look up a tax rate. The LOOKUP function The LOOKUP function looks in a one-row or one-column range (lookup_vector) for a value (lookup_ value) and returns a value from the same position in a second one-row or one-column range (result_vector). The LOOKUP function has the following syntax: LOOKUP(lookup_value,lookup_vector,result_vector) 314

Chapter 14: Creating Formulas That Look Up Values The function’s arguments are as follows: l lookup_value: The value to be looked up in the lookup_vector. l lookup_vector: A single-column or single-row range that contains the values to be looked up. These values must be in ascending order. l result_vector: The single-column or single-row range that contains the values to be returned. It must be the same size as the lookup_vector. Caution Values in the lookup_vector must be in ascending order. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns #N/A. Figure 14.4 shows the tax table again. This time, the formula in cell B3 uses the LOOKUP function to return the corresponding tax rate. The formula in cell B3 is =LOOKUP(B2,D2:D7,F2:F7) Caution If the values in the first column are not arranged in ascending order, the LOOKUP function may return an incorrect value. n Note that LOOKUP (as opposed to VLOOKUP) requires two range references (a range to be looked in, and a range that contains result values). VLOOKUP, on the other hand, uses a single range for the lookup table, and the third argument determines which column to use for the result. This argument, of course, can consist of a cell reference. FIGURE 14.4 Using LOOKUP to look up a tax rate. 315

Part II: Working with Formulas and Functions Combining the MATCH and INDEX functions The MATCH and INDEX functions are often used together to perform lookups. The MATCH function returns the relative position of a cell in a range that matches a specified value. The syntax for MATCH is MATCH(lookup_value,lookup_array,match_type) The MATCH function’s arguments are as follows: l lookup_value: The value you want to match in lookup_array. If match_type is 0 and the lookup_value is text, this argument can include wildcard characters * and ? l lookup_array: The range being searched. l match_type: An integer (–1, 0, or 1) that specifies how the match is determined. Note If match_type is 1, MATCH finds the largest value less than or equal to lookup_value. (lookup_array must be in ascending order.) If match_type is 0, MATCH finds the first value exactly equal to lookup_value. If match_type is –1, MATCH finds the smallest value greater than or equal to lookup_value. (lookup_array must be in descend- ing order.) If you omit the match_type argument, this argument is assumed to be 1. The INDEX function returns a cell from a range. The syntax for the INDEX function is INDEX(array,row_num,column_num) The INDEX function’s arguments are as follows: l array: A range l row_num: A row number within array l col_num: A column number within array Note If array contains only one row or column, the corresponding row_num or column_num argument is optional. n Figure 14.5 shows a worksheet with dates, day names, and amounts in columns D, E, and F. When you enter a date in cell B1, the following formula (in cell B2) searches the dates in column D and returns the corresponding amount from column F. The formula in cell B2 is =INDEX(F2:F21,MATCH(B1,D2:D21,0)) To understand how this formula works, start with the MATCH function. This function searches the range D2:D21 for the date in cell B1. It returns the relative row number where the date is found. This value is then used as the second argument for the INDEX function. The result is the corre- sponding value in F2:F21. 316

Chapter 14: Creating Formulas That Look Up Values FIGURE 14.5 Using the INDEX and MATCH functions to perform a lookup. When a Blank Is Not a Zero The Excel lookup functions treat empty cells in the result range as zeros. The worksheet in the accom- panying figure contains a two-column lookup table, and this formula looks up the name in cell B1 and returns the corresponding amount: =VLOOKUP(B1,D2:E8,2) Note that the Amount cell for Charlie is blank, but the formula returns a 0. continued 317

Part II: Working with Formulas and Functions continued If you need to distinguish zeros from blank cells, you must modify the lookup formula by adding an IF function to check whether the length of the returned value is 0. When the looked up value is blank, the length of the return value is 0. In all other cases, the length of the returned value is non-zero. The fol- lowing formula displays an empty string (a blank) whenever the length of the looked-up value is zero and the actual value whenever the length is anything but zero: =IF(LEN(VLOOKUP(B1,D2:E8,2))=0,””,(VLOOKUP(B1,D2:E8,2))) Alternatively, you can specifically check for an empty string, as in the following formula: =IF(VLOOKUP(B1,D2:E8,2)=””,””,(VLOOKUP(B1,D2:E8,2))) Specialized Lookup Formulas You can use additional types of lookup formulas to perform more specialized lookups. For exam- ple, you can look up an exact value, search in another column besides the first in a lookup table, perform a case-sensitive lookup, return a value from among multiple lookup tables, and perform other specialized and complex lookups. Looking up an exact value As demonstrated in the previous examples, VLOOKUP and HLOOKUP don’t necessarily require an exact match between the value to be looked up and the values in the lookup table. An example is looking up a tax rate in a tax table. In some cases, you may require a perfect match. For example, when looking up an employee number, you would require a perfect match for the number. To look up an exact value only, use the VLOOKUP (or HLOOKUP) function with the optional fourth argument set to FALSE. Figure 14.6 shows a worksheet with a lookup table that contains employee numbers (column C) and employee names (column D). The lookup table is named EmpList. The formula in cell B2, which follows, looks up the employee number entered in cell B1 and returns the corresponding employee name: =VLOOKUP(B1,EmpList,2,FALSE) Because the last argument for the VLOOKUP function is FALSE, the function returns a value only if an exact match is found. If the value is not found, the formula returns #N/A. This result, of course, 318

Chapter 14: Creating Formulas That Look Up Values is exactly what you want to happen because returning an approximate match for an employee number makes no sense. Also, notice that the employee numbers in column C are not in ascending order. If the last argument for VLOOKUP is FALSE, the values need not be in ascending order. Tip If you prefer to see something other than #N/A when the employee number is not found, you can use the IFERROR function to test for the error result and substitute a different string. The following formula displays the text Not Found rather than #N/A: =IFERROR(VLOOKUP(B1,EmpList,2,FALSE),”Not Found”) IFERROR works only with Excel 2007 and Excel 2010. For compatibility with previous versions, use the fol- lowing formula: =IF(ISNA(VLOOKUP(B1,EmpList,2,FALSE)),”Not Found”, VLOOKUP(B1,EmpList,2,FALSE)) FIGURE 14.6 This lookup table requires an exact match. Looking up a value to the left The VLOOKUP function always looks up a value in the first column of the lookup range. But what if you want to look up a value in a column other than the first column? It would be helpful if you could supply a negative value for the third argument for VLOOKUP — but Excel doesn’t allow it. Figure 14.7 illustrates the problem. Suppose that you want to look up the batting average (column B, in a range named Averages) of a player in column C (in a range named Players). The player you want data for appears in a cell named LookupValue. The VLOOKUP function won’t work because the data isn’t arranged correctly. One option is to rearrange your data, but sometimes that’s not possible. One solution is to use the LOOKUP function, which requires two range arguments. The following formula (in cell F3) returns the batting average from column B of the player name contained in the cell named LookupValue: =LOOKUP(LookupValue,Players,Averages) 319

Part II: Working with Formulas and Functions Using the LOOKUP function requires that the lookup range (in this case, the Players range) is in ascending order. In addition to this limitation, the formula suffers from a slight problem: If you enter a nonexistent player (in other words, the LookupValue cell contains a value not found in the Players range), the formula returns an erroneous result. A better solution uses the INDEX and MATCH functions. The formula that follows works just like the previous one except that it returns #N/A if the player is not found. Another advantage is that the player names need not be sorted. =INDEX(Averages,MATCH(LookupValue,Players,0)) FIGURE 14.7 The VLOOKUP function can’t look up a value in column B, based on a value in column C. Performing a case-sensitive lookup The Excel lookup functions (LOOKUP, VLOOKUP, and HLOOKUP) are not case sensitive. For example, if you write a lookup formula to look up the text budget, the formula considers any of the following a match: BUDGET, Budget, or BuDgEt. Figure 14.8 shows a simple example. Range D2:D7 is named Range1, and range E2:E7 is named Range2. The word to be looked up appears in cell B1 (named Value). FIGURE 14.8 Using an array formula to perform a case-sensitive lookup. 320

Chapter 14: Creating Formulas That Look Up Values The array formula that follows is in cell B2. This formula does a case-sensitive lookup in Range1 and returns the corresponding value in Range2. {=INDEX(Range2,MATCH(TRUE,EXACT(Value,Range1),0))} The formula looks up the word DOG (uppercase) and returns 300. The following standard LOOKUP formula (which is not case sensitive) returns 400: =LOOKUP(Value,Range1,Range2) Note When entering an array formula, remember to use Ctrl+Shift+Enter. n Looking up a value from multiple lookup tables You can, of course, have any number of lookup tables in a worksheet. In some situations, your for- mula may need to decide which lookup table to use. Figure 14.9 shows an example. This workbook calculates sales commission and contains two lookup tables: G3:H9 (named CommTable1) and J3:K8 (named CommTable2). The commission rate for a particular sales repre- sentative depends on two factors: the sales rep’s years of service (column B) and the amount sold (column C). Column D contains formulas that look up the commission rate from the appropriate table. For example, the formula in cell D2 is =VLOOKUP(C2,IF(B2<3,CommTable1,CommTable2),2) FIGURE 14.9 This worksheet demonstrates the use of multiple lookup tables. The second argument for the VLOOKUP function consists of an IF formula that uses the value in column B to determine which lookup table to use. The formula in column E simply multiplies the sales amount in column C by the commission rate in column D. The formula in cell E2, for example, is =C2*D2 321

Part II: Working with Formulas and Functions Determining letter grades for test scores A common use of a lookup table is to assign letter grades for test scores. Figure 14.10 shows a worksheet with student test scores. The range E2:F6 (named GradeList) displays a lookup table used to assign a letter grade to a test score. Column C contains formulas that use the VLOOKUP function and the lookup table to assign a grade based on the score in column B. The formula in cell C2, for example, is =VLOOKUP(B2,GradeList,2) When the lookup table is small (as in the example shown earlier in Figure 14.10), you can use a literal array in place of the lookup table. The formula that follows, for example, returns a letter grade without using a lookup table. Rather, the information in the lookup table is hard-coded into an array. See Chapter 17 for more information about arrays. =VLOOKUP(B2,{0,”F”;40,”D”;70,”C”;80,”B”;90,”A”},2) Another approach, which uses a more legible formula, is to use the LOOKUP function with two array arguments: =LOOKUP(B2,{0,40,70,80,90},{“F”,”D”,”C”,”B”,”A”}) FIGURE 14.10 Looking up letter grades for test scores. Calculating a grade-point average A student’s grade-point average (GPA) is a numerical measure of the average grade received for classes taken. This discussion assumes a letter grade system, in which each letter grade is assigned a numeric value (A=4, B=3, C=2, D=1, and F=0). The GPA comprises an average of the numeric 322

Chapter 14: Creating Formulas That Look Up Values grade values weighted by the credit hours of the course. A one-hour course, for example, receives less weight than a three-hour course. The GPA ranges from 0 (all Fs) to 4.00 (all As). Figure 14.11 shows a worksheet with information for a student. This student took five courses, for a total of 13 credit hours. Range B2:B6 is named CreditHours. The grades for each course appear in column C. (Range C2:C6 is named Grades.) Column D uses a lookup formula to calculate the grade value for each course. The lookup formula in cell D2, for example, follows. This formula uses the lookup table in G2:H6 (named GradeTable). =VLOOKUP(C2,GradeTable,2,FALSE) FIGURE 14.11 Using multiple formulas to calculate a GPA. Formulas in column E calculate the weighted values. The formula in cell E2 is =D2*B2 Cell B8 computes the GPA by using the following formula: =SUM(E2:E6)/SUM(B2:B6) The preceding formulas work fine, but you can streamline the GPA calculation quite a bit. In fact, you can use a single array formula to make this calculation and avoid using the lookup table and the formulas in columns D and E. This array formula does the job: {=SUM((MATCH(Grades,{“F”,”D”,”C”,”B”,”A”},0)-1)*CreditHours) /SUM(CreditHours)} Performing a two-way lookup Figure 14.12 shows a worksheet with a table that displays product sales by month. To retrieve sales for a particular month and product, the user enters a month in cell B1 and a product name in cell B2. 323

Part II: Working with Formulas and Functions FIGURE 14.12 This table demonstrates a two-way lookup. To simplify things, the worksheet uses the following named ranges: Month B1 Product B2 Table D1:H14 MonthList D1:D14 ProductList D1:H1 The following formula (in cell B4) uses the MATCH function to return the position of the Month within the MonthList range. For example, if the month is January, the formula returns 2 because January is the second item in the MonthList range (the first item is a blank cell, D1). =MATCH(Month,MonthList,0) The formula in cell B5 works similarly but uses the ProductList range. =MATCH(Product,ProductList,0) The final formula, in cell B6, returns the corresponding sales amount. It uses the INDEX function with the results from cells B4 and B5. =INDEX(Table,B4,B5) You can, of course, combine these formulas into a single formula, as shown here: =INDEX(Table,MATCH(Month,MonthList,0),MATCH(Product,ProductList,0)) 324

Chapter 14: Creating Formulas That Look Up Values Tip Another way to accomplish a two-way lookup is to provide a name for each row and column of the table. A quick way to do so is to select the table and choose Formulas ➪ Defined Names ➪ Create from Selection. In the Create Names from Selection dialog box, select the Top Row and Left Column check boxes. After creating the names, you can use a simple formula, such as: = Sprockets July This formula, which uses the range intersection operator (a space), returns July sales for Sprockets. See Chapter 10 for details about the range intersection operator. n Performing a two-column lookup Some situations may require a lookup based on the values in two columns. Figure 14.13 shows an example. FIGURE 14.13 This workbook performs a lookup by using information in two columns (D and E). The lookup table contains automobile makes and models and a corresponding code for each. The worksheet uses named ranges, as shown here: F2:F12 Code B1 Make B2 Model D2:D12 Makes E2:E12 Models 325

Part II: Working with Formulas and Functions The following array formula displays the corresponding code for an automobile make and model: {=INDEX(Code,MATCH(Make&Model,Makes&Models,0))} This formula works by concatenating the contents of Make and Model and then searching for this text in an array consisting of the concatenated corresponding text in Makes and Models. Determining the cell address of a value within a range Most of the time, you want your lookup formula to return a value. You may, however, need to determine the cell address of a particular value within a range. For example, Figure 14.14 shows a worksheet with a range of numbers that occupies a single column (named Data). Cell B1, which contains the value to look up, is named Target. FIGURE 14.14 The formula in cell B2 returns the address in the Data range for the value in cell B1. The formula in cell B2, which follows, returns the address of the cell in the Data range that con- tains the Target value: =ADDRESS(ROW(Data)+MATCH(Target,Data,0)-1,COLUMN(Data)) If the Data range occupies a single row, use this formula to return the address of the Target value: =ADDRESS(ROW(Data),COLUMN(Data)+MATCH(Target,Data,0)-1) If the Data range contains more than one instance of the Target value, the address of the first occurrence is returned. If the Target value isn’t found in the Data range, the formula returns #N/A. 326

Chapter 14: Creating Formulas That Look Up Values Looking up a value by using the closest match The VLOOKUP and HLOOKUP functions are useful in the following situations: l You need to identify an exact match for a target value. Use FALSE as the function’s fourth argument. l You need to locate an approximate match. If the function’s fourth argument is TRUE or omitted and an exact match is not found, the next largest value less than the lookup value is returned. But what if you need to look up a value based on the closest match? Neither VLOOKUP nor HLOOKUP can do the job. Figure 14.15 shows a worksheet with student names in column A and values in column B. Range B2:B20 is named Data. Cell E2, named Target, contains a value to search for in the Data range. Cell E3, named ColOffset, contains a value that represents the column offset from the Data range. FIGURE 14.15 This workbook demonstrates how to perform a lookup by using the closest match. The array formula that follows identifies the closest match to the Target value in the Data range and returns the names of the corresponding student in column A (that is, the column with an off- set of –1). The formula returns Leslie (with a matching value of 8,000, which is the one closest to the Target value of 8,025). {=INDIRECT(ADDRESS(ROW(Data)+MATCH(MIN(ABS(Target-Data)), ABS(Target-Data),0)-1,COLUMN(Data)+ColOffset))} 327

Part II: Working with Formulas and Functions If two values in the Data range are equidistant from the Target value, the formula uses the first one in the list. The value in ColOffset can be negative (for a column to the left of Data), positive (for a column to the right of Data), or 0 (for the actual closest match value in the Data range). To understand how this formula works, you need to understand the INDIRECT function. This function’s first argument is a text string in the form of a cell reference (or a reference to a cell that contains a text string). In this example, the text string is created by the ADDRESS function, which accepts a row and column reference and returns a cell address. 328

CHAPTER Creating Formulas for Financial Applications I IN THIS CHAPTER t’s a safe bet that the most common use of Excel is to perform calcula- tions involving money. Every day, people make hundreds of thousands of financial decisions based on the numbers that are calculated in a spreadsheet. These decisions range from simple (Can I afford to buy a new A brief overview of the Excel functions that deal with the car?) to complex (Will purchasing XYZ Corporation result in a positive cash flow time value of money in the next 18 months?). This chapter discusses basic financial calculations that you can perform with the assistance of Excel. Formulas that perform various types of loan calculations Formulas that perform various The Time Value of Money types of investment calculations The face value of money may not always be what it seems. A key consider- An overview of Excel ation is the time value of money. This concept involves calculating the value depreciation functions of money in the past, present, or future. It is based on the premise that money increases in value over time because of interest earned by the money. In other words, a dollar invested today will be worth more tomorrow. For example, imagine that your rich uncle decided to give away some money and asked you to choose one of the following options: l Receive $8,000 today. l Receive $9,500 in one year. l Receive $12,000 in five years. l Receive $150 per month for five years. If your goal is to maximize the amount received, you need to take into account not only the face value of the money but also the time value of the money when it arrives in your hands. 329

Part II: Working with Formulas and Functions The time value of money depends on your perspective. In other words, you’re either a lender or a borrower. When you take out a loan to purchase an automobile, you’re a borrower, and the insti- tution that provides the funds to you is the lender. When you invest money in a bank savings account, you’re a lender; you’re lending your money to the bank, and the bank is borrowing it from you. Several concepts contribute to the time value of money: l Present Value (PV): This is the principal amount. If you deposit $5,000 in a bank savings account, this amount represents the principal, or present value, of the money you invested. If you borrow $15,000 to purchase a car, this amount represents the principal or present value of the loan. Present Value may be positive or negative. l Future Value (FV): This is the principal plus interest. If you invest $5,000 for five years and earn 3 percent annual interest, your investment is worth $5,796.37 at the end of the five-year term. This amount is the future value of your $5,000 investment. If you take out a three-year auto loan for $15,000 and make monthly payments based on a 5.25 percent annual interest rate, you pay a total of $16,244.97. This amount represents the principal plus the interest you paid. Future Value may be positive or negative, depending on the perspective (lender or borrower). l Payment (PMT): This is either principal or principal plus interest. If you deposit $100 per month into a savings account, $100 is the payment. If you have a monthly mortgage payment of $1,025, this amount is made up of principal and interest. l Interest Rate: Interest is a percentage of the principal, usually expressed on an annual basis. For example, you may earn 2.5 percent annual interest on a bank CD (certificate of deposit). Or your mortgage loan may have a 6.75 percent interest rate. l Period: This represents the point in time when interest is paid or earned (for example, a bank CD that pays interest quarterly, or an auto loan that requires monthly payments). l Term: This is the amount of time of interest. A 12-month bank CD has a term of one year. A 30-year mortgage loan has a term of 360 months. Loan Calculations This section describes how to calculate various components of a loan. Think of a loan as consisting of the following components: l The loan amount l The interest rate l The number of payment periods l The periodic payment amount If you know any three of these components, you can create a formula to calculate the unknown component. 330

Chapter 15: Creating Formulas for Financial Applications Note The loan calculations in this section all assume a fixed-rate loan with a fixed term. n Worksheet functions for calculating loan information This section describes six commonly used financial functions: PMT, PPMT, IPMT, RATE, NPER, and PV. For information about the arguments used in these functions, see Table 15.1. TABLE 15.1 Financial Function Arguments Function Argument Description rate The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods. nper The total number of payment periods. per A particular period. The period must be less than or equal to nper. pmt The payment made each period (a constant value that does not change). fv The future value after the last payment is made. If you omit fv, it is assumed to be 0. (The future value of a loan, for example, is 0.) type Indicates when payments are due — either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0. PMT The PMT function returns the loan payment (principal plus interest) per period, assuming constant payment amounts and a fixed interest rate. The syntax for the PMT function is PMT(rate,nper,pv,fv,type) The following formula returns the monthly payment amount for a $5,000 loan with a 6 percent annual percentage rate. The loan has a term of four years (48 months). =PMT(6%/12,48,-5000) This formula returns $117.43, the monthly payment for the loan. The first argument, rate, is the annual rate divided by the number of months in a year. Also, notice that the third argument (pv, for present value) is negative and represents money owed. PPMT The PPMT function returns the principal part of a loan payment for a given period, assuming con- stant payment amounts and a fixed interest rate. The syntax for the PPMT function is PPMT(rate,per,nper,pv,fv,type) 331

Part II: Working with Formulas and Functions The following formula returns the amount paid to principal for the first month of a $5,000 loan with a 6 percent annual percentage rate. The loan has a term of four years (48 months). =PPMT(6%/12,1,48,-5000) The formula returns $92.43 for the principal, which is about 78.7 percent of the total loan pay- ment. If I change the second argument to 48 (to calculate the principal amount for the last pay- ment), the formula returns $116.84, or about 99.5 percent of the total loan payment. Note To calculate the cumulative principal paid between any two payment periods, use the CUMPRINC function. This function uses two additional arguments: start_period and end_period. In Excel versions prior to Excel 2007, CUMPRINC is available only when you install the Analysis ToolPak add-in. n IPMT The IPMT function returns the interest part of a loan payment for a given period, assuming con- stant payment amounts and a fixed interest rate. The syntax for the IPMT function is IPMT(rate,per,nper,pv,fv,type) The following formula returns the amount paid to interest for the first month of a $5,000 loan with a 6 percent annual percentage rate. The loan has a term of four years (48 months). =IPMT(6%/12,1,48,-5000) This formula returns an interest amount of $25.00. By the last payment period for the loan, the interest payment is only $0.58. Note To calculate the cumulative interest paid between any two payment periods, use the CUMIPMT function. This function uses two additional arguments: start_period and end_period. In Excel versions prior to Excel 2007, CUMIPMT is available only when you install the Analysis ToolPak add-in. n RATE The RATE function returns the periodic interest rate of a loan, given the number of payment peri- ods, the periodic payment amount, and the loan amount. The syntax for the RATE function is RATE(nper,pmt,pv,fv,type,guess) The following formula calculates the annual interest rate for a 48-month loan for $5,000 that has a monthly payment amount of $117.43. =RATE(48,117.43,-5000)*12 This formula returns 6.00 percent. Notice that the result of the function is multiplied by 12 to get the annual percentage rate. 332

Chapter 15: Creating Formulas for Financial Applications NPER The NPER function returns the number of payment periods for a loan, given the loan’s amount, interest rate, and periodic payment amount. The syntax for the NPER function is NPER(rate,pmt,pv,fv,type) The following formula calculates the number of payment periods for a $5,000 loan that has a monthly payment amount of $117.43. The loan has a 6 percent annual interest rate. =NPER(6%/12,117.43,-5000) This formula returns 47.997 (that is, 48 months). The monthly payment was rounded to the near- est penny, causing the minor discrepancy. PV The PV function returns the present value (that is, the original loan amount) for a loan, given the interest rate, the number of periods, and the periodic payment amount. The syntax for the PV function is PV(rate,nper,pmt,fv,type) The following formula calculates the original loan amount for a 48-month loan that has a monthly payment amount of $117.43. The annual interest rate is 6 percent. =PV(6%/12,48,-117.43) This formula returns $5,000.21. The monthly payment was rounded to the nearest penny, causing the $0.21 discrepancy. A loan calculation example Figure 15.1 shows a worksheet set up to calculate the periodic payment amount for a loan. The loan amount is in cell B1, and the annual interest rate is in cell B2. Cell B3 contains the pay- ment period expressed in months. For example, if cell B3 is 1, the payment is due monthly. If cell B3 is 3, the payment is due every three months, or quarterly. Cell B4 contains the number of peri- ods of the loan. The example shown in this figure calculates the payment for a $25,000 loan at 6.25 percent annual interest with monthly payments for 36 months. The formula in cell B6 is =PMT(B2*(B3/12),B4,-B1) 333

Part II: Working with Formulas and Functions FIGURE 15.1 Using the PMT function to calculate a periodic loan payment amount. Notice that the first argument is an expression that calculates the periodic interest rate by using the annual interest rate and the payment period. Therefore, if payments are made quarterly on a three- year loan, the payment period is 3, the number of periods is 12, and the periodic interest rate would be calculated as the annual interest rate multiplied by 3/12. In the worksheet in Figure 15.1, range A9:B11 is set up to calculate the principal and interest amount for a particular payment period. Cell B9 contains the payment period used by the formulas in B10:B11. (The payment period must be less than or equal to the value in cell B4.) The formula in cell B10, shown here, calculates the amount of the payment that goes toward prin- cipal for the payment period in cell B9: =PPMT(B2*(B3/12),B9,B4,-B1) The following formula, in cell B11, calculates the amount of the payment that goes toward interest for the payment period in cell B9: =IPMT(B2*(B3/12),B9,B4,-B1) You should note that the sum of B10 and B11 is equal to the total loan payment calculated in cell B6. However, the relative proportion of principal and interest amounts varies with the payment period. (An increasingly larger proportion of the payment is applied toward principal as the loan progresses.) Figure 15.2 shows the principal and interest portions graphically. Credit card payments Do you ever wonder how long it would take to pay off a credit card balance if you make the mini- mum payment amount each month? Figure 15.3 shows a worksheet set up to make this type of calculation. 334

Chapter 15: Creating Formulas for Financial Applications FIGURE 15.2 This chart shows the relative interest and principal amounts for the payment periods of a loan. Range B1:B5 stores input values. In this example, the credit card has a balance of $1,000, and the lender charges 21.25 percent annual percentage rate (APR). The minimum payment is 2.00 per- cent (typical of many credit card lenders). Therefore, the minimum payment amount for this example is $20. You can enter a different payment amount in cell B5, but it must be large enough to pay off the loan. For example, you may choose to pay $50 per month to pay off the balance more quickly. However, paying $10 per month isn’t sufficient, and the formulas return an error. Range B7:B9 holds formulas that perform various calculations. The formula in cell B7, which fol- lows, calculates the number of months required to pay off the balance: =NPER(B2/12,B5,-B1,0) The formula in B8 calculates the total amount you will pay. This formula is =B7*B5 The formula in cell B9 calculates the total interest paid: =B8-B1 335

Part II: Working with Formulas and Functions FIGURE 15.3 This worksheet calculates the number of payments required to pay off a credit card balance by paying the minimum payment amount each month. In this example, it would take about 123 months (more than ten years) to pay off the credit card balance if the borrower made only the minimum monthly payment. The total interest paid on the $1,000 loan would be $1,468.42. This calculation assumes, of course, that no additional charges are made on the account. This example may help explain why you receive so many credit card solicitations in the mail. Figure 15.4 shows some additional calculations for the credit card example. For example, if you want to pay off the credit card in 12 months, you need to make monthly payments of $93.23. (This amount results in total payments of $1,118.81 with total interest of $118.81.) The formula in B13 is =PMT($B$2/12,A13,-$B$1) FIGURE 15.4 Column B shows the payment required to pay off the credit card balance for various payoff periods. 336

Chapter 15: Creating Formulas for Financial Applications Creating a loan amortization schedule A loan amortization schedule is a table of values that shows various types of information for each payment period of a loan. Figure 15.5 shows a worksheet that uses formulas to calculate an amor- tization schedule. FIGURE 15.5 A loan amortization schedule. The loan parameters are entered into B1:B4, and the formulas beginning in row 9 use these values for the calculations. Table 15.2 shows the formulas in row 9 of the schedule. These formulas were copied down to row 488. Therefore, the worksheet can calculate amortization schedules for a loan with as many as 480 payment periods (40 years of monthly payments). Note Formulas in the rows that extend beyond the number of payments return an error value. The worksheet uses conditional formatting to hide the data in these rows. See Chapter 20 for more information about conditional formatting. n 337

Part II: Working with Formulas and Functions TABLE 15.2 Formulas Used to Calculate an Amortization Schedule Cell Formula Description A9 =A8+1 Returns the payment number B9 =PMT($B$2*($B$3/12),$B$4,-$B$1) Calculates the periodic payment amount C9 =C8+B9 Calculates the cumulative payment amounts D9 =IPMT($B$2*($B$3/12),A9,$B$4,-$B$1) Calculates the interest portion of the periodic payment E9 =E8+D9 Calculates the cumulative interest paid F9 =PPMT($B$2*($B$3/12),A9,$B$4,-$B$1) Calculates the principal portion of the periodic payment G9 =G8+F9 Calculates the cumulative amount applied toward principal H9 =H8-F9 Returns the principal balance at the end of the period Summarizing loan options by using a data table The Excel Data Table feature is probably one of the most underutilized tools in Excel. Keep in mind that a data table is not the same as a table (created with Insert ➪ Tables ➪ Table). A data table is a handy way to summarize calculations that depend on one or two “changing” cells. In this example, I use a data table to summarize various loan options. This section describes how to create one-way and two-way data tables. Cross-Reference See Chapter 36 for more information about setting up data tables. n Creating a one-way data table A one-way data table shows the results of any number of calculations for different values of a single input cell. Figure 15.6 shows a one-way data table (in B10:I13) that displays three calculations (payment amount, total payments, and total interest) for a loan, using seven interest rates ranging from 7.00 percent to 8.50 percent. In this example, the input cell is cell B2. 338

Chapter 15: Creating Formulas for Financial Applications FIGURE 15.6 Using a one-way data table to display three loan calculations for various interest rates. To create this one-way data table, follow these steps: 1. Enter the formulas that return the results for use in the data table. In this example, the formulas are in B6:B8. 2. Enter various values for a single input cell in successive columns. In this example, the input value is interest rate, and the values for various interest rates appear in C10:I10. 3. Create a reference to the formula cells in the column to the left of the input values. In this example, the range B11:B13 contains simple formulas that reference other cells. For example, cell B11 contains the following formula: =B6 4. Select the rectangular range that contains the entries from the previous steps. In this example, select B10:I13. 5. Choose Data ➪ Data Tools ➪ What-If Analysis ➪ Data Table. Excel displays the Data Table dialog box, shown in Figure 15.7. FIGURE 15.7 The Data Table dialog box. 6. For the Row input cell field, specify the cell reference that corresponds to the vari- able in your Data Table column header row. In this example, the Row input cell is B2. 7. Leave the Column input cell field empty. The Column input field is used for two-way data tables, described in the next section. 339

Part II: Working with Formulas and Functions 8. Click OK. Excel inserts an array formula that uses the TABLE function with a single argument. 9. (Optional) Format the data table. For example, you may want to apply shading to the row and column headers. Note that the array formula is not entered into the entire range that you selected in Step 4. The first column and first row of your selection are not changed. Tip When you create a data table, the leftmost column of the data table (the column that contains the references entered in Step 3) contains the calculated values for the input cell. In this example, those values are repeated in column D. You may want to hide the values in column B by making the font color the same color as the background. n Creating a two-way data table A two-way data table shows the results of a single calculation for different values of two input cells. Figure 15.8 shows a two-way data table (in B10:I16) that displays a calculation (payment amount) for a loan, using seven interest rates and six loan amounts. To create this two-way data table, follow these steps: 1. Enter a formula that returns the results that will be used in the data table. In this example, the formula is in cell B6. The formulas in B7:B8 are not used. 2. Enter various values for the first input in successive columns. In this example, the first input value is interest rate, and the values for various interest rates appear in C10:I10. FIGURE 15.8 Using a two-way data table to display payment amounts for various loan amounts and interest rates. 340

Chapter 15: Creating Formulas for Financial Applications 3. Enter various values for the second input cell in successive rows, to the left and below the input values for the first input. In this example, the second input value is the loan amount, and the values for various loan amounts are in B11:B16. 4. Create a reference to the formula that will be calculated in the table. This reference goes in the upper-left corner of the data table range. In this example, cell B10 contains the following formula: =B6 5. Select the rectangular range that contains the entries from the previous steps. In this example, select B10:I16. 6. Choose Data ➪ Data Tools ➪ What-If Analysis ➪ Data Table. Excel displays the Data Table dialog box. (Refer to Figure 15.7.) 7. For the Row Input Cell field, specify the cell reference that corresponds to the first input cell. In this example, the Row Input cell is B2. 8. For the Column Input Cell field, specify the cell reference that corresponds to the second input cell. In this example, the ColumnInput cell is B1. 9. Click OK. Excel inserts an array formula that uses the TABLE function with two arguments. After you create the two-way data table, you can change the calculated cell by changing the cell ref- erence in the upper-left cell of the data table. In this example, you can change the formula in cell B10 to =B8 so that the data table displays total interest rather than payment amounts. Tip If you create very large data tables, the calculation speed of your workbook may be slowed down. Excel has a special calculation mode for calculation-intensive data tables. To change the calculation mode, choose Formulas ➪ Calculation ➪ Calculation Options ➪ Automatic Except For Data Tables. n Calculating a loan with irregular payments So far, the loan calculation examples in this chapter have involved loans with regular periodic pay- ments. In some cases, loan payback is irregular. For example, you may loan some money to a friend without a formal agreement as to how he will pay the money back. You still collect interest on the loan, so you need a way to perform the calculations based on the actual payment dates. Figure 15.9 shows a worksheet set up to keep track of such a loan. The annual interest rate for the loan is stored in cell B1 (named APR). The original loan amount and loan date are stored in row 5. Formulas, beginning in row 6, track the irregular loan payments and perform calculations. 341

Part II: Working with Formulas and Functions FIGURE 15.9 This worksheet tracks loan payments that are made on an irregular basis. Column B stores the payment amount made on the date in column C. Notice that the payments are not made on a regular basis. Also, notice that in two cases (row 11 and row 24), the payment amount is negative. These entries represent additional borrowed money added to the loan balance. Formulas in columns D and E calculate the amount of the payment credited toward interest and principal. Columns F and G keep a running tally of the cumulative payments and interest amounts. Formulas in column H compute the new loan balance after each payment. Table 15.3 lists and describes the formulas in row 6. Note that each formula uses an IF function to determine whether the payment date in column C is missing. If so, the formula returns an empty string, so no data appears in the cell. TABLE 15.3 Formulas to Calculate a Loan with Irregular Payments Cell Formula Description D6 =IF(C6<>””,(C6- The formula calculates the interest, based on the payment date. C5)/365*H5*APR,””) E6 =IF(C6<>””,B6-D6,””) The formula subtracts the interest amount from the payment to calculate the amount credited to principal. 342

Chapter 15: Creating Formulas for Financial Applications Cell Formula Description F6 =IF(C6<>””,F5+B6,””) The formula adds the payment amount to the running total. G6 =IF(C6<>””,G5+D6,””) The formula adds the interest to the running total. H6 =IF(C6<>””,H5-E6,””) The formula calculates the new loan balance by subtracting the principal amount from the previous loan balance. Investment Calculations Investment calculations involve calculating interest on fixed-rate investments, such as bank savings accounts, CDs, and annuities. You can make these interest calculations for investments that consist of a single deposit or multiple deposits. Future value of a single deposit Many investments consist of a single deposit that earns interest over the term of the investment. This section describes calculations for simple interest and compound interest. Calculating simple interest Simple interest refers to the fact that interest payments are not compounded. The basic formula for computing interest is Interest = Principal * Rate * Term For example, suppose that you deposit $1,000 into a bank CD that pays a 3 percent simple annual interest rate. After one year, the CD matures, and you withdraw your money. The bank adds $30, and you walk away with $1,030. In this case, the interest earned is calculated by multiplying the principal ($1,000) by the interest rate (.03) by the term (one year). If the investment term is less than one year, the simple interest rate is adjusted accordingly, based on the term. For example, $1,000 invested in a six-month CD that pays 3 percent simple annual interest earns $15.00 when the CD matures. In this case, the annual interest rate multiplies by 6/12. 343

Part II: Working with Formulas and Functions Figure 15.10 shows a worksheet set up to make simple interest calculations. The formula in cell B7, shown here, calculates the interest due at the end of the term: =B3*B4*B5 The formula in B8 simply adds the interest to the original investment amount. FIGURE 15.10 This worksheet calculates simple interest payments. Calculating compound interest Most fixed-term investments pay interest by using some type of compound interest calculation. Compound interest refers to interest credited to the investment balance, and the investment then earns interest on the interest. For example, suppose that you deposit $1,000 into a bank CD that pays 3 percent annual interest rate, compounded monthly. Each month, the interest is calculated on the balance, and that amount is credited to your account. The next month’s interest calculation will be based on a higher amount because it also includes the previous month’s interest payment. One way to calculate the final investment amount involves a series of formulas (see Figure 15.11). Column B contains formulas to calculate the interest for one month. For example, the formula in B10 is =C9*($B$5*(1/12)) The formulas in column C simply add the monthly interest amount to the balance. For example, the formula in C10 is =C9+B10 At the end of the 12-month term, the CD balance is $1,030.42. In other words, monthly com- pounding results in an additional $0.42 (compared with simple interest). 344

Chapter 15: Creating Formulas for Financial Applications FIGURE 15.11 Using a series of formulas to calculate compound interest. You can use the FV (Future Value) function to calculate the final investment amount without using a series of formulas. Figure 15.12 shows a worksheet set up to calculate compound interest. Cell B6 is an input cell that holds the number of compounding periods per year. For monthly com- pounding, the value in B6 would be 12. For quarterly compounding, the value would be 4. For daily compounding, the value would be 365. Cell B7 holds the term of the investment expressed in years. FIGURE 15.12 Using a single formula to calculate compound interest. 345

Part II: Working with Formulas and Functions Cell B9 contains the following formula that calculates the periodic interest rate. This value is the interest rate used for each compounding period. =B5*(1/B6) The formula in cell B10 uses the FV function to calculate the value of the investment at the end of the term. The formula is =FV(B9,B6*B7,,-B4) The first argument for the FV function is the periodic interest rate, which is calculated in cell B9. The second argument represents the total number of compounding periods. The third argument (pmt) is omitted, and the fourth argument is the original investment amount (expressed as a nega- tive value). The total interest is calculated with a simple formula in cell B11: =B10-B4 Another formula, in cell B13, calculates the annual yield on the investment: =(B11/B4)/B7 For example, suppose that you deposit $5,000 into a three-year CD with a 4.25 percent annual interest rate compounded quarterly. In this case, the investment has four compounding periods per year, so you enter 4 into cell B6. The term is three years, so you enter 3 into cell B7. The for- mula in B10 returns $5,676.11. Perhaps you want to see how this rate stacks up against a competitor’s account that offers daily compounding. Figure 15.13 shows a calculation with daily compounding, using a $5,000 invest- ment (compare this with Figure 15.12). As you can see, the difference is very small ($679.88 ver- sus $676.11). Over a period of three years, the account with daily compounding earns a total of $3.77 more interest. In terms of annual yield, quarterly compounding earns 4.51%, and daily com- pounding earns 4.53%. Calculating interest with continuous compounding The term continuous compounding refers to interest that is accumulated continuously. In other words, the investment has an infinite number of compounding periods per year. The following for- mula calculates the future value of a $5,000 investment at 4.25 percent compounded continuously for three years: =5000*EXP(4.25%*3) The formula returns $5,679.92, which is an additional $0.04 compared with daily compounding. 346

Chapter 15: Creating Formulas for Financial Applications FIGURE 15.13 Calculating interest by using daily compounding. Note You can calculate compound interest without using the FV function. The general formula to calculate com- pound interest is Principal * (1 + periodic rate) ^ number of periods For example, consider a five-year, $5,000 investment that earns an annual interest rate of 4 percent, com- pounded monthly. The formula to calculate the future value of this investment is =5000*(1+4%/12)^(12*5) The Rule of 72 Need to make an investment decision, but don’t have a computer handy? You can use the Rule of 72 to determine the number of years required to double your money at a particular interest rate, using annual compounding. Just divide 72 by the interest rate. For example, consider a $10,000 investment at 4 percent interest. How many years will it take to turn that 10 grand into 20 grand? Take 72, divide it by 4, and you get 18 years. What if you can get a 5 percent interest rate? If so, you can double your money in a little over 14 years. How accurate is the Rule of 72? The table that follows shows Rule of 72 estimated values versus the actual values for various interest rates. As you can see, this simple rule is remarkably accurate. However, for interest rates that exceed 30 percent, the accuracy drops off considerably. continued 347

Part II: Working with Formulas and Functions continued Interest Rate Rule of 72 Actual 1% 72.00 69.66 2% 36.00 35.00 3% 24.00 23.45 4% 18.00 17.67 5% 14.40 14.21 6% 12.00 11.90 7% 10.29 10.24 8% 9.00 9.01 9% 8.00 8.04 10% 7.20 7.27 15% 4.80 4.96 20% 3.60 3.80 25% 2.88 3.11 30% 2.40 2.64 The Rule of 72 also works in reverse. For example, if you want to double your money in six years, divide 6 into 72; you’ll discover that you need to find an investment that pays an annual interest rate of about 12 percent. Good luck. Future value of a series of deposits Now, consider another type of investment, one in which you make a regular series of deposits into an account. This type of investment is known as an annuity. The worksheet functions discussed in the “Loan Calculations” section earlier in this chapter also apply to annuities, but you need to use the perspective of a lender, not a borrower. A simple exam- ple of this type of investment is a holiday club savings program offered by some banking institu- tions. A fixed amount is deducted from each of your paychecks and deposited into an interest-earning account. At the end of the year, you withdraw the money (with accumulated inter- est) to use for holiday expenses. Suppose that you deposit $200 at the beginning of each month (for 12 months) into an account that pays 2.5 percent annual interest compounded monthly. The following formula calculates the future value of your series of deposits: =FV(2.5%/12,12,-200,,1) 348

Chapter 15: Creating Formulas for Financial Applications This formula returns $2,432.75, which represents the total of your deposits ($2,400) plus the interest ($32.75). The last argument for the FV function is 1, which means that you make pay- ments at the beginning of the month. Figure 15.14 shows a worksheet set up to calculate annuities. Table 15.4 describes the contents of this sheet. FIGURE 15.14 This worksheet contains formulas to calculate annuities. TABLE 15.4 The Annuity Calculator Worksheet Cell Formula Description B4 None (input cell) Initial investment (can be 0) B5 None (input cell) The amount deposited on a regular basis B6 None (input cell) The number of deposits made in 12 months B7 None (input cell) TRUE if you make deposits at the beginning of period; FALSE otherwise B10 None (input cell) The length of the investment, in years (can be fractional) continued 349

Part II: Working with Formulas and Functions TABLE 15.4 (continued) Cell Formula Description B13 None (input cell) The annual interest rate B16 =B4 Displays the initial investment amount B17 =B5*B6*B10 Calculates the total of all regular deposits B18 =B16+B17 Adds the initial investment to the sum of the deposits B19 =B13*(1/B6) Calculates the periodic interest rate B20 =FV(B19,B6*B10,- Calculates the future value of the investment B5,-B4,IF(B7,1,0)) B21 =B20-B18 Calculates the interest earned from the investment Depreciation Calculations Excel offers five functions to calculate depreciation of an asset over time. Depreciating an asset places a value on the asset at a point in time, based on the original value and its useful life. The function that you choose depends on the type of depreciation method that you use. Table 15.5 summarizes the Excel depreciation functions and the arguments used by each. For complete details, consult the Excel online Help system. TABLE 15.5 Excel Depreciation Functions Function Depreciation Method Arguments* SLN Straight-line. The asset depreciates by the same amount Cost, Salvage, Life each year of its life. DB Declining balance. Computes depreciation at a fixed rate. Cost, Salvage, Life, Period, [Month] DDB Double-declining balance. Computes depreciation at an Cost, Salvage, Life, Period, accelerated rate. Depreciation is highest in the first period [Factor and decreases in successive periods. 350

Chapter 15: Creating Formulas for Financial Applications Function Depreciation Method Arguments* SYD Sum of the year’s digits. Allocates a large depreciation in Cost, Salvage, Life, Period the earlier years of an asset’s life. VDB Variable-declining balance. Computes the depreciation of Cost, Salvage, Life, Start an asset for any period (including partial periods) using the _Period, End_Period, double-declining balance method or some other method [Factor], [No Switch] you specify. * Arguments in brackets are optional. Here are the arguments for the depreciation functions: l Cost: Original cost of the asset. l Salvage: Salvage cost of the asset after it has fully depreciated. l Life: Number of periods over which the asset will depreciate. l Period: Period in the life for which the calculation is being made. l Month: Number of months in the first year; if omitted, Excel uses 12. l Start_Period:Starting period for the depreciation calculation. l End_Period: Ending period for the depreciation calculation. l Factor: Rate at which the balance declines; if omitted, it is assumed to be 2 (that is, double-declining). l No Switch: TRUE or FALSE. Specifies whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation. Figure 15.15 shows depreciation calculations using the SLN, DB, DDB, and SYD functions. The asset’s original cost, $10,000, is assumed to have a useful life of 10 years, with a salvage value of $1,000. The range labeled Depreciation Amount shows the annual depreciation of the asset. The range labeled Value of Asset shows the asset’s depreciated value over its life. Figure 15.16 shows a chart that graphs the asset’s value. As you can see, the SLN function pro- duces a straight line; the other functions produce a curved line because the depreciation is greater in the earlier years of the asset’s life. 351

Part II: Working with Formulas and Functions FIGURE 15.15 A comparison of four depreciation functions. FIGURE 15.16 This chart shows an asset’s value over time, using four depreciation functions. 352

Chapter 15: Creating Formulas for Financial Applications The VBD function is useful if you need to calculate depreciation for multiple periods (for example, years 2 and 3). Figure 15.17 shows a worksheet set up to calculate depreciation using the VBD function. The formula in cell B11 is =VDB(B2,B4,B3,B6,B7,B8,B9) FIGURE 15.17 Using the VBD function to calculate depreciation for multiple periods. The formula displays the depreciation for the first three years of an asset (starting period of 0 and ending period of 3). 353



CHAPTER Introducing Array Formulas O IN THIS CHAPTER ne of Excel’s most interesting (and most powerful) features is its ability to work with arrays in formulas. When you understand this concept, you’ll be able to create elegant formulas that appear to per- form spreadsheet magic. The definition of an array and an array formula This chapter introduces the concept of arrays and is required reading for One-dimensional versus two- anyone who wants to become a master of Excel formulas. Chapter 17 contin- dimensional arrays ues with lots of useful examples. How to work with array constants Techniques for working with Understanding Array Formulas array formulas Examples of multicell array If you do any computer programming, you’ve probably been exposed to the formulas concept of an array. An array is simply a collection of items operated on col- lectively or individually. In Excel, an array can be one dimensional or two Examples of array formulas that occupy a single cell dimensional. These dimensions correspond to rows and columns. For exam- ple, a one-dimensional array can be stored in a range that consists of one row (a horizontal array) or one column (a vertical array). A two-dimensional array can be stored in a rectangular range of cells. Excel doesn’t support three- dimensional arrays (but its VBA programming language does). As you’ll see, arrays need not be stored in cells. You can also work with arrays that exist only in Excel’s memory. You can then use an array formula to manipulate this information and return a result. An array formula can occupy multiple cells or reside in a single cell. 355

Part II: Working with Formulas and Functions This section presents two array formula examples: an array formula that occupies multiple cells and another array formula that occupies only one cell. A multicell array formula Figure 16.1 shows a simple worksheet set up to calculate product sales. Normally, you’d calculate the value in column D (total sales per product) with a formula such as the one that follows, and then you’d copy this formula down the column. =B2*C2 After copying the formula, the worksheet contains six formulas in column D. FIGURE 16.1 Column D contains formulas to calculate the total for each product. An alternative method uses a single formula (an array formula) to calculate all six values in D2:D7. This single formula occupies six cells and returns an array of six values. To create a single array formula to perform the calculations, follow these steps: 1. Select a range to hold the results. In this case, the range is D2:D7. Because you can’t display more than one value in a single cell, six cells are required to display the resulting array — so you select six cells to make this array work. 2. Type the following formula: =B2:B7*C2:C7 3. Press Ctrl+Shift+Enter to enter the formula. Normally, you press Enter to enter a for- mula. Because this is an array formula, however, press Ctrl+Shift+Enter. Caution You can’t insert a multicell array formula into a range that has been designated a table (using Insert ➪ Tables ➪ Table). In addition, you can’t convert a range that contains a multicell array formula to a table. n 356

Chapter 16: Introducing Array Formulas The formula is entered into all six selected cells. If you examine the Formula bar, you see the following: {=B2:B7*C2:C7} Excel places curly brackets around the formula to indicate that it’s an array formula. This formula performs its calculations and returns a six-item array. The array formula actually works with two other arrays, both of which happen to be stored in ranges. The values for the first array are stored in B2:B7, and the values for the second array are stored in C2:C7. This array formula returns exactly the same values as these six normal formulas entered into indi- vidual cells in D2:D7: =B2*C2 =B3*C3 =B4*C4 =B5*C5 =B6*C6 =B7*C7 Using a single array formula rather than individual formulas does offer a few advantages: l It’s a good way to ensure that all formulas in a range are identical. l Using a multicell array formula makes it less likely that you’ll overwrite a formula acciden- tally. You can’t change one cell in a multicell array formula. Excel displays an error mes- sage if you attempt to do so. l Using a multicell array formula will almost certainly prevent novices from tampering with your formulas. Using a multicell array formula as described in the preceding list also has some potential disadvantages: l It’s impossible to insert a new row into the range. But in some cases, the inability to insert a row is a positive feature. For example, you might not want users to add rows because it would affect other parts of the worksheet. l If you add new data to the bottom of the range, you need to modify the array formula to accommodate the new data. A single-cell array formula Now it’s time to take a look at a single-cell array formula. Check out Figure 16.2, which is similar to Figure 16.1. Notice, however, that the formulas in column D have been deleted. The goal is to calculate the sum of the total product sales without using the individual calculations that were in column D. 357

Part II: Working with Formulas and Functions FIGURE 16.2 The array formula in cell C9 calculates the total sales without using intermediate formulas. The following array formula is in cell C9: {=SUM(B2:B7*C2:C7)} When you enter this formula, make sure that you use Ctrl+Shift+Enter (and don’t type the curly brackets because Excel automatically adds them for you). This formula works with two arrays, both of which are stored in cells. The first array is stored in B2:B7, and the second array is stored in C2:C7. The formula multiplies the corresponding values in these two arrays and creates a new array (which exists only in memory). The SUM function then operates on this new array and returns the sum of its values. Note In this case, you can use the SUMPRODUCT function to obtain the same result without using an array formula: =SUMPRODUCT(B2:B7,C2:C7) As you see, however, array formulas allow many other types of calculations that are otherwise not possible. Creating an array constant The examples in the preceding section used arrays stored in worksheet ranges. The examples in this section demonstrate an important concept: An array need not be stored in a range of cells. This type of array, which is stored in memory, is referred to as an array constant. To create an array constant, list its items and surround them with brackets. Here’s an example of a five-item horizontal array constant: {1,0,1,0,1} 358

Chapter 16: Introducing Array Formulas The following formula uses the SUM function, with the preceding array constant as its argument. The formula returns the sum of the values in the array (which is 3): =SUM({1,0,1,0,1}) Notice that this formula uses an array, but the formula itself isn’t an array formula. Therefore, you don’t use Ctrl+Shift+Enter to enter the formula — although entering it as an array formula will still produce the same result. Note When you specify an array directly (as shown previously), you must provide the curly brackets around the array elements. When you enter an array formula, on the other hand, you do not supply the brackets. n At this point, you probably don’t see any advantage to using an array constant. The following for- mula, for example, returns the same result as the previous formula. The advantages, however, will become apparent. =SUM(1,0,1,0,1) This formula uses two array constants: =SUM({1,2,3,4}*{5,6,7,8}) This formula creates a new array (in memory) that consists of the product of the corresponding elements in the two arrays. The new array is {5,12,21,32} This new array is then used as an argument for the SUM function, which returns the result (70). The formula is equivalent to the following formula, which doesn’t use arrays: =SUM(1*5,2*6,3*7,4*8) Alternatively, you can use the SUMPRODUCT function. The formula that follows is not an array for- mula, but it uses two array constants as its arguments. =SUMPRODUCT({1,2,3,4},{5,6,7,8}) A formula can work with both an array constant and an array stored in a range. The following for- mula, for example, returns the sum of the values in A1:D1, each multiplied by the corresponding element in the array constant: =SUM((A1:D1*{1,2,3,4})) This formula is equivalent to =SUM(A1*1,B1*2,C1*3,D1*4) 359

Part II: Working with Formulas and Functions Array constant elements An array constant can contain numbers, text, logical values (TRUE or FALSE), and even error val- ues, such as #N/A. Numbers can be in integer, decimal, or scientific format. You must enclose text in double quotation marks. You can use different types of values in the same array constant, as in this example: {1,2,3,TRUE,FALSE,TRUE,”Moe”,”Larry”,”Curly”} An array constant can’t contain formulas, functions, or other arrays. Numeric values can’t contain dollar signs, commas, parentheses, or percent signs. For example, the following is an invalid array constant: {SQRT(32),$56.32,12.5%} Understanding the Dimensions of an Array As stated previously, an array can be one dimensional or two dimensional. A one-dimensional array’s orientation can be horizontal (corresponding to a single row) or vertical (corresponding to a single column). One-dimensional horizontal arrays The elements in a one-dimensional horizontal array are separated by commas, and the array can be displayed in a row of cells. The following example is a one-dimensional horizontal array constant: {1,2,3,4,5} Displaying this array in a range requires five consecutive cells in a row. To enter this array into a range, select a range of cells that consists of one row and five columns. Then enter ={1,2,3,4,5} and press Ctrl+Shift+Enter. Note If you enter this array into a horizontal range that consists of more than five cells, the extra cells will contain #N/A (which denotes unavailable values). If you enter this array into a vertical range of cells, only the first item (1) will appear in each cell. n The following example is another horizontal array; it has seven elements and is made up of text strings: {“Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”} 360

Chapter 16: Introducing Array Formulas To enter this array, select seven cells in a row and type the following (followed by Ctrl+Shift+Enter): ={“Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”} One-dimensional vertical arrays The elements in a one-dimensional vertical array are separated by semicolons, and the array can be displayed in a column of cells. The following is a six-element vertical array constant: {10;20;30;40;50;60} Displaying this array in a range requires six cells in a column. To enter this array into a range, select a range of cells that consists of six rows and one column. Then enter the following formula, followed by Ctrl+Shift+Enter: ={10;20;30;40;50;60} The following is another example of a vertical array; this one has four elements: {“Widgets”;”Sprockets”;”Doodads”;”Thingamajigs”} Two-dimensional arrays A two-dimensional array uses commas to separate its horizontal elements and semicolons to sepa- rate its vertical elements. The following example shows a 3 × 4 array constant: {1,2,3,4;5,6,7,8;9,10,11,12} Displaying this array in a range requires 12 cells. To enter this array into a range, select a range of cells that consists of three rows and four columns. Then type the following formula, followed by Ctrl+Shift+Enter: ={1,2,3,4;5,6,7,8;9,10,11,12} Figure 16.3 shows how this array appears when entered into a range (in this case, B3:E5). If you enter an array into a range that has more cells than array elements, Excel displays #N/A in the extra cells. Figure 16.4 shows a 3 × 4 array entered into a 10 × 5 cell range. Each row of a two-dimensional array must contain the same number of items. The array that fol- lows, for example, isn’t valid, because the third row contains only three items: {1,2,3,4;5,6,7,8;9,10,11} Excel doesn’t allow you to enter a formula that contains an invalid array. 361

Part II: Working with Formulas and Functions FIGURE 16.3 A 3 × 4 array entered into a range of cells. FIGURE 16.4 A 3 × 4 array entered into a 10 × 5 cell range. Naming Array Constants You can create an array constant, give it a name, and then use this named array in a formula. Technically, a named array is a named formula. Cross-Reference Chapter 3 covers the topic of names and named formulas. n Figure 16.5 shows a named array being created from the New Name dialog box. (Access this dialog box by choosing Formulas ➪ Defined Names ➪ Define Name.) The name of the array is DayNames, and it refers to the following array constant: {“Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”} Notice that, in the New Name dialog box, the array is defined (in the Refers To field) using a lead- ing equal sign (=). Without this equal sign, the array is interpreted as a text string rather than an 362