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