1.) In cell H9 create a VLOOKUP to indicate the description Expensive for pups priced $2,000...

1.) In cell H9 create a VLOOKUP to indicate the description Expensive for pups priced $2,000 or more, Moderate for pups priced $1,000 or more but below $2,000, Good Dealfor pups priced $500 or more but less than $1,000 and Cheapfor all pups priced less than $500. Begin the lookup table in cell E1. Copy the VLOOKUP in cell H9 to cells H10:H202. 2.) In cell I9 create a Nested IF to duplicate the results of #1 above. Copy the nested If in cell I9 to cells I10:I202. 3.) Sort the database by AKC Group and within AKC Group by Breed and within Breed by Color (all ascending order). Create a Subtotal report showing the average price by AKC Group and the total number of pups available for each dog breed. Change to outline level 3. Right-mouse click on the Sheet 1worksheet tab. Select “Move or Copy” and check mark “Create a copy” and click on (move to end). Name the newly created worksheet tab, “Subtotal.” Return to Sheet1and remove the subtotal report by clicking on the “Remove All” button in the Data/Subtotal menu. 4.) Use the “unique records only” feature of the Advanced Filter to indicate the unique varieties of dog colors and breeds. Start the unique dog breeds in K1 and the unique dog colors in M1. Place the unique dog breed and dog color combinations starting in cell O1. (You will have to do three separate Unique Advanced Filters.) 5.) Note that none of the filters in #4 require a criterion. To demonstrate a Unique Filter witha Criteria output the unique breeds with a price of $2,500 or more. Start your criteria in R1. Start your output in R4. Output just the Breed names that meet the criteria. (Check figure: 8) 6.) Insert a new worksheet. On Sheet 3, create a one variable data table that indicates the total number of pups available for each breed and the average price for each breed. (Copy/paste the Unique dog breeds extracted in #4.) Place the northwest corner of the table in cell A5. Begin the required criteria range in cell A1.7.) Insert a new worksheet. On Sheet 4, create a two variable data table that indicates the total number of pups available by dog breed and by dog color. (Use the unique dog breeds & unique colors extracted in #4. Use the copy & paste special / transpose to copy vertical text as horizontal text.) Place the northwest corner of the table in cell A5. Begin the required criteria range in cell A1. 8.) Turn off the display of zeroes in Sheet 4. File ribbon / Excel Options/Advanced (scroll down to Display Options for this Worksheet: Sheet 4) Remove the check mark in front of: Show a zero in cells that have a zero value. 9.) Create a pivot table that indicates the total number of pups available by breed and the average price for each breed. Place the northwest corner of the pivot table in cell E4 on Sheet 3. 10.) Create a pivot table that indicates the total number of pups available by dog breed and by dog color. Place the pivot table as a new worksheet.

Breeder		Dog		No. of	Welp
ID#	AKC Group	Breed	Color	Pups	Date	Price/Pup	Vlookup	IF
912	Non-Sporting	Dalmatian	Black	4	2/13/20	$ 800
749	Toy	Chihuahua	Black	2	8/11/18	$ 475
449	Sporting	Brittany	Tri	3	6/20/20	$ 650
833	Hound	Saluki	White	2	7/7/18	$ 2,500
502	Sporting	Labrador Retriever	Black	10	7/22/20	$ 301
723	Hound	Afghan Hound	White	1	3/23/18	$ 1,500
288	Sporting	Curly-Coated Retriever	Liver	2	11/27/18	$ 600
581	Toy	Miniature Pinscher	Black	7	10/17/19	$ 500
689	Terrier	Bedlington Terrier	Tan	3	4/20/18	$ 950
456	Terrier	Staffordshire Bull Terrier	Black	1	10/28/18	$ 800
414	Herding	Border Collie	Red	1	4/26/20	$ 500
672	Working	Great Dane	Black	6	12/18/20	$ 825
777	Terrier	Smooth Fox Terrier	Tri	7	9/11/19	$ 150
601	Working	Akita	White	1	10/31/19	$ 3,000
555	Sporting	Labrador Retriever	Chocolate	2	4/10/18	$ 150
789	Toy	Pomeranian	Blue	2	7/24/18	$ 1,500
989	Toy	Toy Fox Terrier	Tri	1	3/11/18	$ 199
337	Working	Bernese Mountain Dog	Black	2	1/16/19	$ 1,750
589	Herding	Shetland Sheepdog	Sable	4	11/25/19	$ 1,300
459	Working	Great Dane	Blue	2	10/8/19	$ 2,800
978	Terrier	Bull Terrier	White	4	7/14/18	$ 825
914	Hound	American Foxhound	Tri	5	6/5/20	$ 900
419	Sporting	Irish Setter	Red	7	6/26/18	$ 600
414	Herding	Border Collie	Blue	5	5/29/19	$ 299
648	Terrier	Irish Terrier	Red	3	9/3/19	$ 600
815	Toy	Affenpinscher	Black	2	5/12/18	$ 2,000
382	Toy	Maltese	White	3	4/7/18	$ 800
243	Terrier	Smooth Fox Terrier	Tri	8	1/31/19	$ 85
348	Sporting	German Shorthaired Pointer	Liver	1	9/15/18	$ 350
603	Sporting	Labrador Retriever	Yellow	9	5/31/18	$ 300
178	Hound	Bloodhound	Red	3	1/10/20	$ 1,750
456	Terrier	Staffordshire Bull Terrier	Red	1	9/26/19	$ 800
766	Terrier	Cairn Terrier	Red	3	2/2/18	$ 850
555	Sporting	Labrador Retriever	Black	11	3/25/19	$ 301
288	Sporting	Curly-Coated Retriever	Black	4	5/27/19	$ 600
449	Herding	Cardigan Welsh Corgi	Sable	1	6/5/20	$ 600
651	Sporting	Cocker Spaniel	Tan	4	5/8/20	$ 180
258	Terrier	Staffordshire Bull Terrier	White	2	6/16/20	$ 1,100
414	Herding	Border Collie	Chocolate	2	1/25/18	$ 500
112	Non-Sporting	Chow Chow	Black	5	10/28/19	$ 250
457	Working	Great Pyrenees	White	4	2/19/19	$ 450
749	Toy	Chihuahua	Tan	3	6/30/18	$ 475

Expert Solution

1)

Breeder ID#	AKC Group	Dog Breed	Color	No. of Pups	Welp Date	Price/Pup	Vlookup
912	Non-Sporting	Dalmatian	Black	4	2/13/20	800	Good Deal
749	Toy	Chihuahua	Black	2	08/11/2018	475	Cheap
449	Sporting	Brittany	Tri	3	6/20/20	650.00	Good Deal
833	Hound	Saluki	White	2	07/07/2018	2,500.00	Expensive
502	Sporting	Labrador Retriever	Black	10	7/22/20	301	Cheap
723	Hound	Afghan Hound	White	1	3/23/18	1500	Moderate
288	Sporting	Curly-Coated Retriever	Liver	2	11/27/18	600.00	Good Deal
581	Toy	Miniature Pinscher	Black	7	10/17/19	500.00	Good Deal
689	Terrier	Bedlington Terrier	Tan	3	4/20/18	950.00	Good Deal
456	Terrier	Staffordshire Bull Terrier	Black	1	10/28/18	800	Good Deal
414	Herding	Border Collie	Red	1	4/26/20	500
672	Working	Great Dane	Black	6	12/18/20	825	Good Deal
777	Terrier	Smooth Fox Terrier	Tri	7	09/11/2019	150	Cheap
601	Working	Akita	White	1	10/31/19	3,000.00	Expensive
555	Sporting	Labrador Retriever	Chocolate	2	04/10/2018	150	Cheap
789	Toy	Pomeranian	Blue	2	7/24/18	1500	Moderate
989	Toy	Toy Fox Terrier	Tri	1	03/11/2018	199	Cheap
337	Working	Bernese Mountain Dog	Black	2	1/16/19	1750	Moderate
589	Herding	Shetland Sheepdog	Sable	4	11/25/19	1300	Good Deal
459	Working	Great Dane	Blue	2	10/08/2019	2,800.00	Expensive
978	Terrier	Bull Terrier	White	4	7/14/18	825	Good Deal
914	Hound	American Foxhound	Tri	5	06/05/2020	900	Good Deal
419	Sporting	Irish Setter	Red	7	6/26/18	600	Good Deal
414	Herding	Border Collie	Blue	5	5/29/19	299	Cheap
648	Terrier	Irish Terrier	Red	3	09/03/2019	600.00	Good Deal
815	Toy	Affenpinscher	Black	2	05/12/2018	2,000.00	Expensive
382	Toy	Maltese	White	3	04/07/2018	800.00	Good Deal
243	Terrier	Smooth Fox Terrier	Tri	8	1/31/19	85.00	Cheap
348	Sporting	German Shorthaired Pointer	Liver	1	9/15/18	350.00	Cheap
603	Sporting	Labrador Retriever	Yellow	9	5/31/18	300.00	Cheap
178	Hound	Bloodhound	Red	3	01/10/2020	1,750.00	Moderate
456	Terrier	Staffordshire Bull Terrier	Red	1	9/26/19	800.00	Good Deal
766	Terrier	Cairn Terrier	Red	3	02/02/2018	850.00	Good Deal
555	Sporting	Labrador Retriever	Black	11	3/25/19	301.00	Cheap
288	Sporting	Curly-Coated Retriever	Black	4	5/27/19	600.00	Good Deal
449	Herding	Cardigan Welsh Corgi	Sable	1	06/05/2020	600.00	Good Deal
651	Sporting	Cocker Spaniel	Tan	4	05/08/2020	180.00	Cheap
258	Terrier	Staffordshire Bull Terrier	White	2	6/16/20	1,100.00	Moderate
414	Herding	Border Collie	Chocolate	2	1/25/18	500.00	Good Deal
112	Non-Sporting	Chow Chow	Black	5	10/28/19	250.00	Cheap
457	Working	Great Pyrenees	White	4	2/19/19	450.00	Cheap
749	Toy	Chihuahua	Tan	3	6/30/18	475.00	Cheap

10)

Row Labels	Average of Price/Pup
Affenpinscher	2000
Afghan Hound	1500
Akita	3000
American Foxhound	900
Bedlington Terrier	950
Bernese Mountain Dog	1750
Bloodhound	1750
Border Collie	433
Brittany	650
Bull Terrier	825
Cairn Terrier	850
Cardigan Welsh Corgi	600
Chihuahua	475
Chow Chow	250
Cocker Spaniel	180
Curly-Coated Retriever	600
Dalmatian	800
German Shorthaired Pointer	350
Great Dane	1812.5
Great Pyrenees	450
Irish Setter	600
Irish Terrier	600
Labrador Retriever	263
Maltese	800
Miniature Pinscher	500
Pomeranian	1500
Saluki	2500
Shetland Sheepdog	1300
Smooth Fox Terrier	117.5
Staffordshire Bull Terrier	900
Toy Fox Terrier	199
Grand Total	853.9285714

ekkarill92 answered 6 months ago

1. If a cell cannot properly create DNA polymerase which step of replication would this cell...

1. If a cell cannot properly create DNA polymerase which step of replication would this cell be incapable of performing? Explain why 2. It is possible to compare transcription to a race car moving along a track. In transcription which units or structures would be analogous to the following: the car, a green light, the track, and a stop sign? 3. Describe the relationship between the sequence of the mRNA molecule and the amino acid sequence of the resulting protein.

1. In 144 characters or less, create a brief job description for future managers. 2. Housing...

1. In 144 characters or less, create a brief job description for future managers. 2. Housing for the homeless is a problem in many communities. In what way might this problem be addressed through some form of social business innovation? Again, be creative on how an organization can turn to innovation to maybe not advance the company, but advance society. If you are stumped on ideas, go to the Internet. Many businesses have already taken on this challenge!

1. Discuss factors that can precipitate a sickle cell crisis. 2. Create a care plan for...

1. Discuss factors that can precipitate a sickle cell crisis. 2. Create a care plan for the patient presenting in sickle cell crisis. 3. Create an education plan for the patient being discharged following hospitalization for sickle cell crisis.

1) Create a chart that compares and contrasts the structure of gram-positive and gram-negative cell walls....

1) Create a chart that compares and contrasts the structure of gram-positive and gram-negative cell walls. 2) Gram negative bacteria are considered more harmful than gram positive bacteria.What are the two reasons that contribute to this situation due to the unique characteristic of the outer membrane of Gram-negative bacteria? (Minimum length : 150 words)

Question