The city market wants an easier way to keep track of fresh vegetables and their shelf life.
Question:
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?
Essentials of Business Analytics
ISBN: 978-1285187273
1st edition
Authors: Jeffrey Camm, James Cochran, Michael Fry, Jeffrey Ohlmann, David Anderson, Dennis Sweeney, Thomas Williams