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 Deal for pups priced $500 or more but less than $1,000 and Cheap for 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 1 worksheet 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 Sheet1 and 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 with a 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. 11. Make the print settings changes so that rows 7 & 8 will appear at the top of each page if you were to print.A7:I202 (fit-to-3 pages tall). Put your name in the Headers and all sheets and submit your file (instructors flash drive or Blackboard).

Breeder		Dog		No. of	Welp
ID#	AKC Group	Breed	Color	Pups	Date	Price/Pup	Vlookup	IF
912	Non-Sporting	Dalmatian	Black	4	2/13/2020	$ 800
749	Toy	Chihuahua	Black	2	8/11/2018	$ 475
449	Sporting	Brittany	Tri	3	6/20/2020	$ 650
833	Hound	Saluki	White	2	7/7/2018	$ 2,500
502	Sporting	Labrador Retriever	Black	10	7/22/2020	$ 301
723	Hound	Afghan Hound	White	1	3/23/2018	$ 1,500
288	Sporting	Curly-Coated Retriever	Liver	2	11/27/2018	$ 600
581	Toy	Miniature Pinscher	Black	7	10/17/2019	$ 500
689	Terrier	Bedlington Terrier	Tan	3	4/20/2018	$ 950
456	Terrier	Staffordshire Bull Terrier	Black	1	10/28/2018	$ 800
414	Herding	Border Collie	Red	1	4/26/2020	$ 500
672	Working	Great Dane	Black	6	12/18/2020	$ 825
777	Terrier	Smooth Fox Terrier	Tri	7	9/11/2019	$ 150
601	Working	Akita	White	1	10/31/2019	$ 3,000
555	Sporting	Labrador Retriever	Chocolate	2	4/10/2018	$ 150
789	Toy	Pomeranian	Blue	2	7/24/2018	$ 1,500
989	Toy	Toy Fox Terrier	Tri	1	3/11/2018	$ 199
337	Working	Bernese Mountain Dog	Black	2	1/16/2019	$ 1,750
589	Herding	Shetland Sheepdog	Sable	4	11/25/2019	$ 1,300
459	Working	Great Dane	Blue	2	10/8/2019	$ 2,800
978	Terrier	Bull Terrier	White	4	7/14/2018	$ 825
914	Hound	American Foxhound	Tri	5	6/5/2020	$ 900
419	Sporting	Irish Setter	Red	7	6/26/2018	$ 600
414	Herding	Border Collie	Blue	5	5/29/2019	$ 299
648	Terrier	Irish Terrier	Red	3	9/3/2019	$ 600
815	Toy	Affenpinscher	Black	2	5/12/2018	$ 2,000
382	Toy	Maltese	White	3	4/7/2018	$ 800
243	Terrier	Smooth Fox Terrier	Tri	8	1/31/2019	$ 85
348	Sporting	German Shorthaired Pointer	Liver	1	9/15/2018	$ 350
603	Sporting	Labrador Retriever	Yellow	9	5/31/2018	$ 300
178	Hound	Bloodhound	Red	3	1/10/2020	$ 1,750
456	Terrier	Staffordshire Bull Terrier	Red	1	9/26/2019	$ 800
766	Terrier	Cairn Terrier	Red	3	2/2/2018	$ 850
555	Sporting	Labrador Retriever	Black	11	3/25/2019	$ 301
288	Sporting	Curly-Coated Retriever	Black	4	5/27/2019	$ 600
449	Herding	Cardigan Welsh Corgi	Sable	1	6/5/2020	$ 600
651	Sporting	Cocker Spaniel	Tan	4	5/8/2020	$ 180
258	Terrier	Staffordshire Bull Terrier	White	2	6/16/2020	$ 1,100
414	Herding	Border Collie	Chocolate	2	1/25/2018	$ 500
112	Non-Sporting	Chow Chow	Black	5	10/28/2019	$ 250
457	Working	Great Pyrenees	White	4	2/19/2019	$ 450
749	Toy	Chihuahua	Tan	3	6/30/2018	$ 475
754	Toy	Pekingese	Black	1	7/13/2019	$ 850
645	Hound	Petit Basset Griffon Venden	Tri	2	12/12/2019	$ 1,250
639	Working	Rottweiler	Black	6	4/15/2019	$ 300
221	Non-Sporting	Poodle	Apricot	3	11/29/2018	$ 575
135	Non-Sporting	Schipperke	Black	1	11/10/2020	$ 1,350
833	Hound	Saluki	Tri	2	8/8/2018	$ 2,000
978	Terrier	Bull Terrier	Black	1	3/25/2019	$ 825
893	Non-Sporting	Bichon Frise	White	1	1/12/2019	$ 2,800
346	Hound	Dachshund	Black	10	12/14/2019	$ 275
362	Toy	Brussels Griffon	Black	5	12/22/2020	$ 950
723	Hound	Afghan Hound	Black	4	11/7/2018	$ 1,500
589	Toy	Pug	Apricot	1	11/14/2018	$ 1,750
246	Toy	Chihuahua	Tri	6	9/13/2018	$ 375
378	Toy	Shih Tzu	Tan	2	12/26/2019	$ 350
488	Toy	Pug	Black	1	11/3/2018	$ 1,350
551	Sporting	Cocker Spaniel	Black	6	8/12/2020	$ 280
378	Non-Sporting	Poodle	Black	4	12/15/2019	$ 1,500
127	Working	Doberman Pinscher	Blue	5	2/16/2019	$ 500
388	Hound	Irish Wolfhound	Black	1	1/7/2020	$ 1,000
380	Hound	Dachshund	Black	3	1/26/2019	$ 50
663	Working	Samoyed	White	6	1/28/2018	$ 550
362	Toy	Brussels Griffon	Red	2	2/9/2020	$ 950
325	Terrier	Scottish Terrier	Black	1	12/23/2018	$ 950
925	Terrier	West Highland White Terrier	White	3	7/16/2020	$ 800
597	Working	Great Pyrenees	White	1	6/28/2019	$ 300
888	Non-Sporting	Bulldog	Red	1	5/4/2020	$ 4,000
193	Terrier	Kerry Blue Terrier	Blue	5	7/26/2020	$ 775
966	Hound	Basenji	Red	1	3/30/2020	$ 2,250
127	Working	Doberman Pinscher	Black	7	4/9/2020	$ 500
157	Sporting	English Springer Spaniel	Tri	3	11/9/2019	$ 575
893	Terrier	Norwich Terrier	Red	4	2/20/2020	$ 2,000
456	Terrier	Staffordshire Bull Terrier	White	3	4/23/2019	$ 800
389	Hound	Greyhound	Red	1	4/1/2019	$ 450
376	Working	Doberman Pinscher	Black	1	8/24/2018	$ 400
582	Terrier	Scottish Terrier	Black	2	1/13/2018	$ 750
400	Hound	Beagle	Tri	8	8/22/2018	$ 250
912	Non-Sporting	Dalmatian	Liver	1	1/19/2019	$ 800
449	Herding	Cardigan Welsh Corgi	Red	2	2/9/2020	$ 600
749	Toy	Chihuahua	Red	7	3/4/2019	$ 475
157	Sporting	English Springer Spaniel	Black	2	10/29/2018	$ 475
414	Herding	Border Collie	Black	7	7/20/2019	$ 299
976	Toy	Shih Tzu	White	1	2/23/2019	$ 1,000
400	Non-Sporting	Chow Chow	Red	6	11/28/2019	$ 150
925	Toy	Pomeranian	Brown	5	12/21/2018	$ 800
555	Sporting	Welsh Springer Spaniel	Red	1	11/12/2020	$ 425
294	Sporting	German Shorthaired Pointer	Liver	6	8/18/2019	$ 600
925	Toy	Pomeranian	White	2	11/14/2019	$ 800
893	Terrier	Wire Fox Terrier	Tri	6	6/20/2018	$ 375
551	Sporting	Cocker Spaniel	Tri	7	12/21/2019	$ 280
378	Non-Sporting	Poodle	White	3	11/6/2019	$ 1,500
477	Non-Sporting	Schipperke	Black	2	3/27/2020	$ 1,500
589	Herding	Shetland Sheepdog	Tri	1	7/17/2020	$ 1,300
789	Toy	Pomeranian	Red	2	3/7/2020	$ 1,500
844	Sporting	Spinone Italiano	Brown	1	3/28/2018	$ 2,800
119	Sporting	Brittany	Liver	7	6/17/2020	$ 450
916	Toy	Maltese	White	1	9/8/2020	$ 2,000
679	Toy	Italian Greyhound	Blue	3	10/20/2020	$ 375
472	Toy	Italian Greyhound	Black	2	8/27/2020	$ 800
449	Sporting	Brittany	Liver	6	6/6/2019	$ 650
346	Hound	Dachshund	Red	6	8/14/2020	$ 275
449	Herding	Cardigan Welsh Corgi	Tri	3	11/28/2020	$ 500
681	Herding	Old English Sheepdog	Blue	1	1/8/2019	$ 2,750
380	Toy	Pekingese	Sable	2	8/29/2019	$ 300
215	Hound	Beagle	Tri	7	2/16/2020	$ 200
966	Hound	Basenji	Black	1	6/24/2018	$ 2,250
215	Herding	Australian Cattle Dog	Red	6	7/2/2020	$ 149
777	Working	Standard Schnauzer	Black	1	10/29/2020	$ 850
437	Toy	Miniature Pinscher	Chocolate	2	4/16/2019	$ 650
645	Non-Sporting	Chinese Shar-Pei	Red	1	12/5/2020	$ 1,750
741	Working	Newfoundland	Brown	2	8/23/2019	$ 1,800
388	Hound	Irish Wolfhound	Red	1	1/9/2020	$ 1,000
833	Hound	Saluki	Red	5	8/7/2019	$ 1,750
444	Working	Newfoundland	Black	1	3/15/2018	$ 1,200
276	Working	Komondor	White	2	1/11/2020	$ 1,150
672	Herding	Collie	Sable	5	2/25/2018	$ 425
388	Hound	Irish Wolfhound	White	2	3/28/2019	$ 1,000
258	Terrier	Staffordshire Bull Terrier	Red	2	1/25/2020	$ 1,100
978	Terrier	Bull Terrier	Tan	2	8/20/2020	$ 825
380	Toy	Toy Fox Terrier	Tri	3	9/3/2018	$ 325
766	Hound	American Foxhound	Tri	4	9/15/2019	$ 800
488	Toy	Papillon	Tri	1	2/5/2020	$ 1,500
500	Herding	Collie	Tri	4	10/27/2018	$ 375
603	Sporting	Welsh Springer Spaniel	Red	7	1/5/2020	$ 375
482	Terrier	Bull Terrier	White	2	2/17/2018	$ 1,100
400	Non-Sporting	Chow Chow	Red	8	1/2/2019	$ 251
963	Sporting	Clumber Spaniel	White	4	12/21/2018	$ 500
329	Toy	Pekingese	Red	4	12/17/2019	$ 650
222	Non-Sporting	Bichon Frise	White	1	3/1/2018	$ 1,200
472	Toy	Italian Greyhound	Blue	4	7/1/2019	$ 800
380	Hound	Dachshund	Red	4	7/2/2018	$ 50
362	Toy	Brussels Griffon	Grizzle	7	6/20/2018	$ 950
855	Hound	Basset Hound	Tri	2	11/20/2020	$ 750
378	Toy	Shih Tzu	White	2	5/20/2019	$ 350
844	Sporting	Spinone Italiano	White	3	8/16/2020	$ 2,800
364	Herding	Australian Cattle Dog	Blue	3	11/24/2020	$ 80
112	Non-Sporting	Chow Chow	Black	4	6/5/2020	$ 250
893	Hound	Petit Basset Griffon Venden	Tri	1	2/23/2020	$ 1,800
492	Working	Komondor	White	3	9/14/2020	$ 700
379	Terrier	Irish Terrier	Red	2	1/29/2020	$ 600
123	Working	Rottweiler	Black	4	10/28/2018	$ 850
597	Hound	Greyhound	Red	1	12/4/2020	$ 950
378	Non-Sporting	Poodle	Apricot	1	5/4/2019	$ 2,000
893	Terrier	Norfolk Terrier	Red	3	2/20/2018	$ 2,000
633	Terrier	Wire Fox Terrier	Tri	9	1/17/2018	$ 275
178	Hound	Bloodhound	Black	2	10/26/2020	$ 1,750
866	Terrier	West Highland White Terrier	White	2	7/31/2019	$ 350
412	Toy	Maltese	White	2	4/16/2018	$ 1,500
925	Toy	Pomeranian	Black	3	2/17/2020	$ 800
221	Non-Sporting	Poodle	White	8	2/5/2019	$ 575
491	Toy	Brussels Griffon	Grizzle	6	7/14/2020	$ 1,250
389	Hound	Greyhound	Black	1	7/5/2020	$ 450
852	Toy	Yorkshire Terrier	Blue	3	7/1/2019	$ 800
893	Terrier	Norwich Terrier	Black	1	11/22/2019	$ 2,250
364	Herding	Australian Cattle Dog	Red	4	3/11/2018	$ 80
644	Non-Sporting	Chinese Shar-Pei	Black	2	3/1/2020	$ 1,750
115	Working	Samoyed	White	3	12/15/2018	$ 825
651	Sporting	Cocker Spaniel	Black	7	5/28/2018	$ 180
376	Working	Doberman Pinscher	Blue	4	10/24/2018	$ 400
127	Working	Doberman Pinscher	Brown	2	11/4/2018	$ 500
644	Hound	Petit Basset Griffon Venden	Tri	3	7/28/2020	$ 850
569	Sporting	Spinone Italiano	Brown	4	3/8/2018	$ 2,500
414	Herding	Border Collie	Tri	6	8/30/2020	$ 299
761	Toy	Yorkshire Terrier	Blue	2	1/17/2020	$ 2,750
581	Toy	Miniature Pinscher	Chocolate	3	8/23/2018	$ 500
215	Herding	Australian Cattle Dog	Blue	7	2/15/2020	$ 149
376	Working	Doberman Pinscher	Brown	6	11/10/2019	$ 400
925	Working	Bullmastiff	Red	2	8/26/2019	$ 1,950
450	Working	Akita	White	2	8/2/2020	$ 1,500
389	Hound	Greyhound	White	6	8/28/2020	$ 450
679	Toy	Italian Greyhound	Black	1	1/3/2018	$ 375
119	Sporting	Brittany	Tri	2	3/21/2020	$ 450
222	Non-Sporting	Bichon Frise	Apricot	2	2/27/2018	$ 1,200
925	Toy	Papillon	Tri	3	12/14/2019	$ 900
346	Toy	Toy Fox Terrier	Tri	2	11/28/2018	$ 550
569	Sporting	Spinone Italiano	White	2	8/20/2020	$ 2,500
789	Toy	Pomeranian	Sable	2	10/2/2018	$ 1,500
339	Sporting	Chesapeake Bay Retriever	Brown	7	7/5/2020	$ 875
581	Toy	Miniature Pinscher	Blue	1	12/18/2019	$ 500
456	Terrier	Staffordshire Bull Terrier	Blue	2	1/10/2018	$ 800
500	Herding	Collie	Sable	2	5/9/2020	$ 375
378	Toy	Shih Tzu	Black	1	3/29/2020	$ 350
893	Terrier	Norfolk Terrier	Black	2	3/13/2019	$ 2,250
449	Herding	Cardigan Welsh Corgi	Black	1	9/18/2019	$ 500
457	Herding	Shetland Sheepdog	Sable	6	3/27/2018	$ 1,000
987	Working	Rottweiler	Black	7	10/22/2019	$ 550
221	Non-Sporting	Poodle	Black	7	8/24/2020	$ 575
688	Terrier	Bedlington Terrier	Liver	2	6/8/2018	$ 950
744	Sporting	Curly-Coated Retriever	Black	5	4/3/2018	$ 500
581	Toy	Miniature Pinscher	Red	4	5/27/2019	$ 500
723	Hound	Afghan Hound	Tan	3	12/30/2018	$ 1,500
854	Terrier	Kerry Blue Terrier	Blue	4	5/25/2020	$ 850
389	Hound	Greyhound	Blue	4	6/6/2019	$ 450