Problems

1. The ADDRESS function yields the actual cell address associated with a row and column. For example, the formula ADDRESS(3,4) yields $D$3. What result would be obtained if you entered the formula =INDIRECT(ADDRESS(3,4))?

2. The workbook P21_2.xlsx contains data for the sales of five products in four regions (East, West, North, and South). Use the INDIRECT function to create formulas that enable you to easily calculate the total sales of any combination of consecutively numbered products, such as Products 1–3, Products 2–5, and so on.

3. The file P21_3.xlsx contains six worksheets. Sheet i contains month i sales for Products 1–4. These sales are always listed in the range E5:H5. Use the INDIRECT function to efficiently tabulate the sales of each product by month in a separate worksheet.

4. Write a formula that will total the entries in the cell range G2:K2 even if you insert one or more columns between columns G and K.

5. The file Marketbasketdata.xlsx contains sales of various items. For each row, a 1 in columns B through K indicates a purchased item, whereas a 0 marks an item that was not purchased. In the day week column, a 1 means the transactions was on a Monday, a 2 means the transaction was on a Tuesday, and so on. For each item listed in K9:K14, calculate the percentage of transactions for which the item was purchased. Also calculate the fraction of transactions taking place on each day.

6. The file Verizonindirectdata.xlsx contains each employee’s hours of work and employee rating for January–May. Set up a consolidation sheet that enables us to choose any person, and then reports their hours of work during each month along with their overall rating for the month.