In the Labs

Design, create, modify, and/or use a workbook following the guidelines, concepts, and skills presented in this module. Labs 1 and 2, which increase in difficulty, require you to create solutions based on what you learned in the module; Lab 3 requires you to apply your creative thinking and problem-solving skills to design and implement a solution.

Lab 1:

Creating Structured References, a Lookup Table, and a Treemap

Problem: The City Market wants an easier way to keep track of the shelf life of fresh vegetables. You will format and summarize the data as shown in Figure 6–80a, and create the treemap shown in Figure 6–80b.

Figure 6–80a

(a) Table and Analysis

Figure 6–80b

(b) Treemap Chart

1. Open the workbook Lab 6–1 City Market. Save the workbook using the file name, Lab 6–1 City Market Complete. Format the data as a table. Adjust column widths as necessary.

2. Create three new calculated columns, Shelf Life, Days Left, and Grade. The formula for calculating the Shelf Life is = [Sell By Date] − [Stock Date]. The formula for calculating the Days Left is = [Sell By Date] − currentDate. (Hint: currentDate is a named cell, E19.)

3. The Grade column will require you to create a lookup table area, shown in Table 6–6. Type the heading, Grade Table in cell J1 and fill in the column headings and data below that, as shown in Table 6–6. The calculation for the Grade column will use the VLOOKUP function. Recall that in a table, the first argument of the VLOOKUP function references the first cell in the column that you want to look up (such as G3). The second argument is the range of the lookup table with absolute references (such as $J$3:$K$7). The third argument is the column number of the rating within the lookup table (such as 2).

Dataset

Labs