Excel problem, homework help

User Generated

oavebhyn

Business Finance

Description

there is 2 lab for this assignment.. I will provide you the data file for this lab

Unformatted Attachment Preview

W create a solution, which Litigating on your own from treate solutions Lab 1: Using Structured References and a Lookup Table Problem: The city market wants an easier way to You format and summarize the data, as shown in Figure 6-74. keep track of fresh vegetables and their shelf life. B 9. Lab 6-1 City Market Complete xlsx - Excel PAGE LAYOUT INSERT FORMULAS DATA REVIEW HOME VIEW FILE 21 22 Clear O Connections B Properties Refresh All- Edit Links Sort Filter Existing Connections Reapply Advanced From From From From Other Text Sources Text to Flash Remove Data Columns Fill Duplicates Validation Data Tools 02 Access Web Connections Sort & Filter Consolidate What this one Analyan Get External Data City Market A6 B с C D E F G H City Market Grade To 8 Sell By Date 9/26/2014 Shelf Life 18 7 Days left Grade 8 9/24/2014 19 F 0 3 14 D 7 9 10 11 Type Stock Date Vegetable 9/8/2014 Fruit 9/5/2014 Vegetable 9/5/2014 Fruit 9/3/2014 Fruit 9/9/2014 Vegetable 9/6/2014 Vegetable 9/9/2014 Days Left Grade 17 A 15 A 10 B 4 D 10 B 10 B 10 Product ID Produce 369718 Potatoes 471829 Apples 558129 Carrots 716281 Grapes 895241 Oranges 638192 Lettuce 721890 Tomatoes 9/19/2014 9/13/2014 9/19/2014 9/19/2014 Output Area 9 10 14 А 13 12 13 1 1 F 9/10/2014 14 5 D Oulpul Area 10 10 Grade A Count 2 15 987456 7 Grade B Count 3 2 526891 5 9/14/2014 9/14/2014 9/16/2014 9M0/2014 9/17/2014 9/10/2014 9/4/2014 9/4/2014 9/9/2014 9/5/2014 9/7/2014 9/3/2014 Peaches Green Beans Bananas Strawberries Mushrooms Onions Fruit Vegetable Fruit Fruit Vegetable Vegetable 5 D 7 C 1 F 8 C 1 F 17 13 7 821928 Grade C Count Vegetable Count Fruit Count 10 6 16 17 18 19 20 21 22 23 24 7 19 835570 557812 491526 Total 9/9/2014 Current Date ==> results of COUNTIF functions Moga SaveMan RES Figure 6-74 $2 Excel Chapter 6 Creating, Sorting, and Querying a Table In the Labs continued City Market Complete. Format the data as a table. Left is = [Sell By Date) - currentDate. (Hint: currentDate is a named cell, F24.) 1. Open the workbook Lab 6-1 City Market. Save the workbook using the file name, Lab 6-1 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 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 J6 and fill in the column headings and data below that, as shown that in a table, the first argument of the VLOOKUP function references the first cell in the in Table 6-6. The calculation for the Grade column will use the VLOOKUP function. Recall table with absolute references (such as $J$8:$K$12). The third argument is the column # of the column that you want to look up (such as G8). The second argument is the range of the lookup rating within the lookup table. Table 6-6 Grade Table Grade Table Grade Days Left F wo D © 2014 Cengage Learning C 7 B 9 А 14 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”). Table 6-7 Output Area Output Area Grade A Count =countif(h8:20,"A") Grade B Count =countif(h8:h20,"B") Grade C Count =countif(h8:20,"C") © 2014 Cengage Learning Vegetable Count =countif(c8:20,"Vegetable") Fruit Count =countif(c8:c20,"Fruit") 5. Add one more item to the Output Area with the count of your favorite fruit or vegetable . Hun 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 Creating. Sorting, and Querying a Table Excel Chapter 6 EX A03 Lab 2: Querying a Table Vote: To complete this assignment, you will be required to use the Data Files for Students. Visit www.cengage.com/ct/studentdownload for detailed instructions or contact your instructor for information about accessing the required files. Problem: K&L Manufacturing would like the answers to the questions listed below about their Run Excel. Open the workbook Lab 6-2 Sales Rep Table. Save the workbook using the file name, Lab 6-2 Sales Rep Table Complete. Format the data as a table and signs. Fill in the comparison criteria to select records from the list to solve each of the problems. So create both a criteria range and an extract range. Format the quota and sales amounts with dollar that you understand better what is required for this assignment, the answer is given for the 1. Select records that represent sales representatives who are less than 40 years old and are female. The criteria is displayed in Figure 6-75a. The extracted records are displayed in Figure 6-75b. sales staff. Fxcel Chapter Perform the following tasks: first problem. Connections Properties Refresh 1176 A 31 Sort Filter BE Existing Al Cid Line Criteria Range Connections Remove Du Consolidate what this Group Unigroup Sul Duplicates Valid Analysis Dus Tools Due Fum From From From Other Are Web Test Sources Get Det Sorta Connection Region D G C С E H B А Criteria Range K 1 Div # Dist # Rep # Age Gender Region # Quota Fname Sales 2 Lname criteria
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi,I've completed the two lab problems. Please let me know if you have any questions or would like any updates made. It was nice to work with you again. Thank you.Best regards,Peter

Jeff Quasney
In the Lab 5-3 Part 1

City Market
Product ID
369718
471829
558129
716281
895241
638192
721890
987456
526891
821928
835570
557812
491526
Total

Produce
Potatoes
Apples
Carrots
Grapes
Oranges
Lettuce
Tomatoes

Type
Vegetable
Fruit
Vegetable
Fruit
Fruit
Vegetable
Vegetable

Stock Date
9/8/2014
9/5/2014
9/5/2014
9/3/2014
9/9/2014
9/6/2014
9/9/2014

Sell By Date
9/26/2014
9/24/2014
9/19/2014
9/13/2014
9/19/2014
9/19/2014
9/10/2014

Shelf Life
18
19
14
10
10
13
1

Peaches
Green Beans
Bananas
Strawberries
Mushrooms
Onions

Fruit
Vegetable
Fruit
Fruit
Vegetable
Vegetable

9/4/2014
9/4/2014
9/9/2014
9/5/2014
9/7/2014
9/3/2014

9/14/2014
9/14/2014
9/16/2014
9/10/2014
9/17/2014
9/10/2014

10
10
7
5
10
7
19

Current Date ==>

9/9/2014

Days Left

Grade
17
15
10
4
10
10
1

A
A
B
D
B
B
F

5
5
7
1
8
1
17

D
D
C
F
C
...


Anonymous
Just what I needed. Studypool is a lifesaver!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags