The city market wants an easier way to keep track of fresh vegetables and their shelf life. Your format and summarize the data:

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.

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, F24.)

3. The Grade column will require you to create a lookup table area, as shown in Tables 6 – 6. Type the heading, Grade Table, in cell J6 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 G8). The second argument is the range of the lookup table with absolute references (such as $J$8:$K$12). The third argument is column # of the rating within the lookup table.

 4. Create an output area, as shown in Table 6 – 7, using several COUNTIF functions to total the following. Recall that the first argument of the COUNTIF function is the range of data (for instance, the grade ratings in H8:H20, or the type of produce in C8:C20) and the second argument is the desired data (such as “A” or “Vegetable”).

 

5. Add one more item to the Output Area with the count of your favorite fruit or vegetable. (Hint: If you use the correct function and arguments, the count should equal 1.)

6. Save the file again and submit the assignment as requested by your instructor.

7. If you were to add a criteria range to the worksheet, for what kinds of data and conditions would you search? Why? What criteria would be most important to the owner of the City Market?

PUSC THE F A Al @+DADFERE 8 15 FFFFER 500 2 Product ID 309719 471829 10 650129 11 716281 805241 17 13 630192 14 721890 HOME INGZET PAGE LEVOUT TOUTAI C Fm fan Ober bis Teres T Son Co 587456 526891 821929 18 835570 12 557812 20 491526 Total 10 17 G & Oly Market C Produce Potatoes Apples Carrots Grapes Oranges Lettuce Tomatoes M Suudlit 10 Frut Fruit Peaches Frut Green Beans Vegetable Bananas Frut Strawberries Fruit Mushrooms Vegetable Onions Vegetable Type Stock Cate Vegetable Frut Vegetable Vegetable Vegetable DATA trvita NEW S D City Market 9/8/2014 9/5/2014 9/5/2014 9/3/2014 9/9/2014 9/5/2014 9/9/2014 The Fau 9/4/2014 9/4/2014 9/9/2014 9/5/2014 9/7/2014 9/3/2014 -Kampple Tots e Drivaat Cum Tala Sell By Date y M 9/25/2014 9/24/2014 9/19/2014 9/13/2014 9/19/2014 9/19/2014 9/10/2014 9/14/2014 9/14/2014 9/16/2014 9/10/2014 9/17/2014 9/10/2014 Current Date 10 10 7 5 10 5 De Cold Wadi G Shelf Life Days Left Grade 18 17 A 19 15 A 14 10 B 10 4 D 10 13 1 7 19 9/9/2014 Dela And 10 B 10 B 1 F H 5 D 5 D 7 C IF 8 C 1 F 17 13 Curie Grade Table Days left Grade 0 F 3 D 7 9 14 H Output Area Output Area Grade A Count Grade B Count Grade C Count Vegetable Count Fruit Count Sapn