Can we get a solution for this problem? Excel module 6 lab 1: Creating Structured References, Look up Table, and a Treemap, The City Market problem.

Molation on the web? een readers have with HTML tables? Is ICH HTML in the Labs Design, create, modify and skills presente you to create solu to apply your creati ate modify, and/or use a workbook following the quidelines, concepts, ils presented in this module. Labs 1 and 2, which increase in difficulty, require reate solutions based on what you learned in the module; Lab 3 requires you v 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. Oltymarket Grade Table Produce Shelf Life Dayal Grado Day le Grade Type Fruit Grapes Oranges Stock Date 016/2017 9/3/2017 9/9/2017 Sell By Date R42017 13/2017 18.2017 14/2017 16 2017 OBOU 08/2017 Output Red Vegetable Carrots Vegetable Latuce Vegetable Tomatoes Vegetable Green Beans Grade Count Vegetable Count Fruit Count 03/2017 10/2017 COUNTS (a) Table and Analysis EX 364 Excel Module 6 Creating Sorting and Querying a In the laberid STUDENT ASSIGNMENTS City Market Stock Dates /2011 (b) Treemap Chart Figure 6-80 Perform the following tasks: 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: current Date is a named cell, E 19.) 3. The Grade column will require you to create a lookup table area shown in Table 6-6. Types heading. Grade Table in cell Jl and fill in the column headings and data below that, shown in Table 6-6. The calculation for the Grade column will use the VLOOKUP funco Recall that in a table, the first argument of the VLOOKUP function references the first in the column that you want to look up (such as G3). The second argument is the range lookup table with absolute references (such as SJ53:SKS7). The third argument is the number of the rating within the lookup table (such as 2). he ringe of the argument is the column Table 6-6 Grade Table Grade Table Days Left Grade Creating, Sorting, and Querying a Table Excel Modul 4. Create an output area, as shown in Figure 6-80a, using several COUNTIF functions to total the grades, fruits, and vegetables, Recall that the first argument of the COUNTIF function is the range of data (for instance, the grade ratings in H3:H15, or the type of produce in B3:315) and the second argument is the desired data (such as "A" or "Vegetable"). 5. To create the treemap: 1. Sort the table by Type and then select the range B2:F15. b. Select the Treemap chart and apply the second chart style. c. Remove the legend. d. Move the Chart to a new worksheet, named, Treemap Chart. Change the font size of the chart title to 32 and type City Market Stock Dates as the new title. Change the font size of the data labels to 18. Apply the Banner label option to the data series. play the Category name and Value data labels, and separate the labels by placing Value on a new line. 6. If requested to Output Area to do function and are 1. Save the file again ed to do so by your instructor on the City Market tab, add one more item to the fea to display the count of your favorite fruit or vegetable. (Hint: If you use the correct and arguments, the count should equal 1.) e again and submit the assignment as requested by your instructor. were to add a criteria range or filtering technique, to the worksheet, for what kinds conditions would you search? Why? What criteria would be most important to the 8. Oli you were of data and conditi Owner of the City Market? Lab 2: Crep Creating and Ouerying a la Tiles Please contact your Molation on the web? een readers have with HTML tables? Is ICH HTML in the Labs Design, create, modify and skills presente you to create solu to apply your creati ate modify, and/or use a workbook following the quidelines, concepts, ils presented in this module. Labs 1 and 2, which increase in difficulty, require reate solutions based on what you learned in the module; Lab 3 requires you v 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. Oltymarket Grade Table Produce Shelf Life Dayal Grado Day le Grade Type Fruit Grapes Oranges Stock Date 016/2017 9/3/2017 9/9/2017 Sell By Date R42017 13/2017 18.2017 14/2017 16 2017 OBOU 08/2017 Output Red Vegetable Carrots Vegetable Latuce Vegetable Tomatoes Vegetable Green Beans Grade Count Vegetable Count Fruit Count 03/2017 10/2017 COUNTS (a) Table and Analysis EX 364 Excel Module 6 Creating Sorting and Querying a In the laberid STUDENT ASSIGNMENTS City Market Stock Dates /2011 (b) Treemap Chart Figure 6-80 Perform the following tasks: 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: current Date is a named cell, E 19.) 3. The Grade column will require you to create a lookup table area shown in Table 6-6. Types heading. Grade Table in cell Jl and fill in the column headings and data below that, shown in Table 6-6. The calculation for the Grade column will use the VLOOKUP funco Recall that in a table, the first argument of the VLOOKUP function references the first in the column that you want to look up (such as G3). The second argument is the range lookup table with absolute references (such as SJ53:SKS7). The third argument is the number of the rating within the lookup table (such as 2). he ringe of the argument is the column Table 6-6 Grade Table Grade Table Days Left Grade Creating, Sorting, and Querying a Table Excel Modul 4. Create an output area, as shown in Figure 6-80a, using several COUNTIF functions to total the grades, fruits, and vegetables, Recall that the first argument of the COUNTIF function is the range of data (for instance, the grade ratings in H3:H15, or the type of produce in B3:315) and the second argument is the desired data (such as "A" or "Vegetable"). 5. To create the treemap: 1. Sort the table by Type and then select the range B2:F15. b. Select the Treemap chart and apply the second chart style. c. Remove the legend. d. Move the Chart to a new worksheet, named, Treemap Chart. Change the font size of the chart title to 32 and type City Market Stock Dates as the new title. Change the font size of the data labels to 18. Apply the Banner label option to the data series. play the Category name and Value data labels, and separate the labels by placing Value on a new line. 6. If requested to Output Area to do function and are 1. Save the file again ed to do so by your instructor on the City Market tab, add one more item to the fea to display the count of your favorite fruit or vegetable. (Hint: If you use the correct and arguments, the count should equal 1.) e again and submit the assignment as requested by your instructor. were to add a criteria range or filtering technique, to the worksheet, for what kinds conditions would you search? Why? What criteria would be most important to the 8. Oli you were of data and conditi Owner of the City Market? Lab 2: Crep Creating and Ouerying a la Tiles Please contact your