Problems

All data for Problems 1–5 is in the Chapter74data.xlsx file.

1. The Duplicate worksheet contains two lists of names. Use an array formula to count the number of names appearing on both lists.

2. The Find Errors worksheet contains some calculations. Use an array formula to count the number of cells containing errors. (Hint: Use the ISERROR function in your array formula.)

3. The Sales worksheet contains 48 months of sales at a toy store. Create an array formula to add (beginning with Month 3) every fifth month of sales. (Hint: You might want to use the Excel MOD function. MOD(number,divisor) yields the remainder after the number is divided by the divisor. For example, MOD(7,5) yields 2.)

4. Use an array function to compute the third, fifth, and seventh power of each month’s sales.

5. The Product worksheet contains sales during April through August of products 1–7. Sales for each month are listed in the same column. Rearrange the data so that sales for each month are listed in the same row and changes to the original data are reflected in the new arrangement you have created.

6. Use the data in the Historicalinvest.xlsx file to create a count of the number of years in which stock, bond, and T-Bill returns are from –20 percent through –15 percent, –15 percent through –10 percent, and so on.

7. An m by n matrix is a rectangular array of numbers containing m rows and n columns. For example,

   

   is a 3-by-3 matrix. Consider two matrices, A and B. Suppose that the number of columns in matrix A equals the number of rows in matrix B. Then you can multiply matrix A by matrix B. (The product is written as AB.) The entry in row I and column J of AB is computed by applying the SUMPRODUCT function to row I of A and column J of B. AB will have as many rows as A and columns as B. The Excel MMULT function is an array function with which you can multiply matrices. Use the MMULT function to multiply the following matrices:

   

8. A square matrix has the same number of rows and columns. Given a square matrix A, suppose there exists a matrix B whereby AB equals a matrix in which each diagonal entry equals 1 and all other entries equal 0. We then say that B is the inverse of A. The Excel array function MINVERSE finds the inverse of a square matrix. Use the MINVERSE function to find the inverse for matrices A and B in Problem 7.

9. Suppose we have invested a fraction f_i of our money in investment i (i=1,2,…,n). Also, suppose the standard deviation of the annual percentage return on investment i is s_i and the correlation between the annual percentage return on investment i and investment j is ñ_ij. We would like to know the variance and standard deviation of the annual percentage return on our portfolio. This can easily be computed by using matrix multiplication. Create the following three matrices:

   • Matrix 1 equals a 1 by n matrix whose ith entry is s_if_i.

   • Matrix 2 equals an n by n matrix whose entry in row i and column j is ñ_ij.

   • Matrix 3 is a n by 1 matrix whose ith entry is s_if_i.

   The variance of the annual percentage return on our portfolio is simply (Matrix 1)*(Matrix 2)*(Matrix 3). The data in Historicalinvest.xlsx gives annual returns on stocks, bonds, and T-Bills. Use the MMULT and TRANSPOSE functions to estimate (based on the given historical data) the variance and standard deviation of a portfolio that invests 50 percent in stocks, 25 percent in bonds, and 25 percent in T-Bills.

   Problems 10–13 use the data in the Makeupdb.xlsx file.

10. How many dollars’ worth of lip gloss did Jen sell?

11. What was the average number of lipstick units sold by Jen in the East region?

12. How many dollars of sales were made by Emilee or in the East region?

13. How many dollars’ worth of lipstick were sold by Colleen or Zaret in the East region?

14. Use the data in the Chapter52data.xlsx file to estimate the trend and seasonal components of the quarterly revenues of Ford and GM.

15. In the toy store example (using the Toysrustrend.xlsx file), use the data for 1999–2001 to forecast quarterly revenues for 2002.

16. The Lillydata.xlsx file contains information from a market research survey that was used to gather insights to aid in designing a new blood pressure drug. Fifteen experts (six from Lilly and nine from other companies-see column N) were asked to compare five sets of four potential Lilly products. The fifth choice in each scenario is that a competitor’s drug is chosen over the four listed Lilly drugs.

    For example, in the first scenario, the second option considered would be a Lilly drug that reduced blood pressure 18 points, resulted in 14 percent of side effects, and sold for $16.

    The range I5:N21 contains the choices each expert made for each of the five scenarios. For example, the first expert (who worked for Lilly) chose a competitor’s drug when faced with Scenario 1 and chose the first listed drug when faced with Scenario 2. Use this information to answer the following:

    • Enter a formula that can be copied from I2 to I2:M5 that calculates the price for each scenario and option in I2:M5.

    • Enter an array formula in I23 that can be copied to I23:I32 and then to J23:M32 that calculates for each question the frequency of each response (1–5), broken down by Lilly and non-Lilly experts. Thus for Question 1, one Lilly expert responded 1, three responded 2, and two responded 5.

17. The Arrayexam1data.xlsx file contains sales by company and date. Your job is to break sales down on a quarterly basis by using array formulas.

    I want the data summarized (using only array formulas) by company and by quarter as shown in Figure 74-12.

    
    Figure 74-12: Format for Problem 17 answer

    For example, L7 should contain Quarter 1 (January 1 through March 31) ACS sales, and so on. Verify your answer with a PivotTable.

18. Explain why array-entering the formula =SUM(1/COUNTIF(Info,Info)) will yield the number of unique entries in the range Info. Apply this formula to the data in the Unique.xlsx file and verify that it returns the number of unique entries.

19. The Salaries.xlsx file contains the salaries of NBA players. Write an array formula that adds the four largest player salaries. Hint: Use the array constant {1,2,3,4} in conjunction with the LARGE function. Then generalize your formula so that you can enter any positive integer n and your formula will add the n largest salaries. Hint: If cell G9 contains an integer n, then when you array-enter a formula ROW(Indirect(“1:”&G9)), Excel will create an array constant {1,2,…,n}.